﻿using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using CrazyTalk.Common;
using CrazyTalk.Common.StringExtension;
using CrazyTalk.Data;
using CrazyTalk.IDAL;
using CrazyTalk.Models;
using CrazyTalk.Models.EnumList;
namespace CrazyTalk.DAL
{
    //UserInfo
    public class UserInfoDAL : IUserInfoDAL
    {
        public bool Exists(string UserId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from UserInfo");
            strSql.Append(" where ");
            strSql.Append(" UserId = @UserId  ");
            SqlParameter[] parameters = {
					new SqlParameter("@UserId", SqlDbType.VarChar,20)			
                                        };
            parameters[0].Value = UserId;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        public bool ExistsEmail(string email)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from UserInfo");
            strSql.Append(" where ");
            strSql.Append(" EMail = @EMail  ");
            SqlParameter[] parameters = {
					new SqlParameter("@EMail", SqlDbType.VarChar,100)			
                                        };
            parameters[0].Value = email;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 根据输入的省市区，获取该地区的区域独代
        /// </summary>
        /// <param name="province">必填</param>
        /// <param name="city">-1表示仅查询省独代</param>
        /// <param name="area">-1表示仅查询市独代</param>
        /// <returns></returns>
        public UserInfo GetAgent(int province, int city, int area)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select b.* from AgentArea as a
left join UserInfo as b on a.UserId=b.UserId
where a.Province=@Province");
            List<SqlParameter> lsp = new List<SqlParameter>();
            lsp.Add(new SqlParameter("@Province", SqlDbType.Int) { Value = province });
            if (city != -1)
            {
                strSql.Append(" and a.City=@City");
                lsp.Add(new SqlParameter("@City", SqlDbType.Int) { Value = city });
                if (area != -1)
                {
                    strSql.Append(" and a.Area=@Area");
                    lsp.Add(new SqlParameter("@Area", SqlDbType.Int) { Value = area });
                }
            }
            DataTable dtb = DbHelperSQL.GetDataTable(strSql.ToString(), lsp.ToArray());
            if (dtb == null || dtb.Rows.Count == 0)
                return null;
            return createModel(dtb.Rows[0]);
        }
        #region 添加记录
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(UserInfo model)
        {
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("insert into UserInfo(");
            ////strSql.Append("UserId,NickName,RealityName,Integral,ParentId,RoleId,PswHash,PswSalt,PswFormat,Address,QQ,EMail,TelPhone,Mobile,Birthday,IDcards,Sex,AmountTotal,AmountInUse,UserStatus,ExpiredDate,CreateTime,StampUserId");
            //strSql.Append("UserId,Birthday,NickName,RealityName,Integral,ParentId,RoleId,PswHash,PswSalt,PswFormat,Address,QQ,EMail,TelPhone,Mobile,IDcards,Sex,AmountTotal,AmountInUse,UserStatus,ExpiredDate,CreateTime,StampUserId ");
            //strSql.Append(") values (");
            ////strSql.Append("@UserId,@NickName,@RealityName,@Integral,@ParentId,@RoleId,@PswHash,@PswSalt,@PswFormat,@Address,@QQ,@EMail,@TelPhone,@Mobile,@Birthday,@IDcards,@Sex,@AmountTotal,@AmountInUse,@UserStatus,@ExpiredDate,@CreateTime,@StampUserId");
            //strSql.Append("@UserId,@Birthday,@NickName,@RealityName,@Integral,@ParentId,@RoleId,@PswHash,@PswSalt,@PswFormat,@Address,@QQ,@EMail,@TelPhone,@Mobile,@IDcards,@Sex,@AmountTotal,@AmountInUse,@UserStatus,@ExpiredDate,@CreateTime,@StampUserId ");
            //strSql.Append(") ");
            //SqlParameter[] parameters = {
            //            new SqlParameter("@UserId", SqlDbType.VarChar,20) ,            
            //            new SqlParameter("@NickName", SqlDbType.NVarChar,20) ,            
            //            new SqlParameter("@RealityName", SqlDbType.NVarChar,10) ,            
            //            new SqlParameter("@Integral", SqlDbType.Int,4) ,            
            //            new SqlParameter("@ParentId", SqlDbType.VarChar,20) ,            
            //            new SqlParameter("@RoleId", SqlDbType.Int,4) ,            
            //            new SqlParameter("@PswHash", SqlDbType.VarChar,50) ,            
            //            new SqlParameter("@PswSalt", SqlDbType.VarChar,50) ,            
            //            new SqlParameter("@PswFormat", SqlDbType.Int,4) ,            
            //            //new SqlParameter("@PswExpiredDate", SqlDbType.DateTime) ,            
            //            //new SqlParameter("@Province", SqlDbType.Int,4) ,            
            //            //new SqlParameter("@City", SqlDbType.Int,4) ,            
            //            //new SqlParameter("@Area", SqlDbType.Int,4) ,            
            //            new SqlParameter("@Address", SqlDbType.NVarChar,50) ,            
            //            new SqlParameter("@QQ", SqlDbType.VarChar,10) ,            
            //            new SqlParameter("@EMail", SqlDbType.VarChar,100) ,            
            //            new SqlParameter("@TelPhone", SqlDbType.VarChar,100) ,            
            //            new SqlParameter("@Mobile", SqlDbType.VarChar,100) ,            
            //            new SqlParameter("@Birthday", SqlDbType.DateTime) ,            
            //            new SqlParameter("@IDcards", SqlDbType.VarChar,18) ,           
            //            new SqlParameter("@HeadImg", SqlDbType.VarChar,150) ,            
            //            new SqlParameter("@Sex", SqlDbType.Int,4) ,            
            //            new SqlParameter("@AmountTotal", SqlDbType.Money,8) ,            
            //            new SqlParameter("@AmountInUse", SqlDbType.Money,8) ,            
            //            //new SqlParameter("@Balance", SqlDbType.Money,8) ,            
            //            //new SqlParameter("@Credit", SqlDbType.Money,8) ,            
            //            new SqlParameter("@UserStatus", SqlDbType.Int,4) ,            
            //            new SqlParameter("@ExpiredDate", SqlDbType.DateTime) ,            
            //            new SqlParameter("@CreateTime", SqlDbType.DateTime),             
            //            new SqlParameter("@StampUserId", SqlDbType.NVarChar,20)          
            //            //new SqlParameter("@UpdateCount", SqlDbType.Int,4) ,            
            //            //new SqlParameter("@ValidateStatus", SqlDbType.Int,4) ,            
            //            //new SqlParameter("@ValidateCode", SqlDbType.VarChar,6) ,  
            //};
            //parameters[0].Value = model.UserId;
            //parameters[1].Value = model.NickName;
            //parameters[2].Value = model.RealityName;
            //parameters[3].Value = model.Integral;
            //parameters[4].Value = model.ParentId;
            //parameters[5].Value = model.RoleId;
            //parameters[6].Value = model.PswHash;
            //parameters[7].Value = model.PswSalt;
            //parameters[8].Value = model.PswFormat;
            ////parameters[9].Value = model.PswExpiredDate;
            ////parameters[10].Value = model.Province;
            ////parameters[11].Value = model.City;
            ////parameters[12].Value = model.Area;
            //parameters[9].Value = model.Address;
            //parameters[10].Value = model.QQ;
            //parameters[11].Value = model.EMail;
            //parameters[12].Value = model.TelPhone;
            //parameters[13].Value = model.Mobile;
            //parameters[14].Value = model.Birthday;//测试占位
            //parameters[15].Value = model.IDcards;
            //parameters[16].Value = model.HeadImg;
            //parameters[17].Value = model.Sex;
            //parameters[18].Value = model.AmountTotal;
            //parameters[19].Value = model.AmountInUse;
            ////parameters[24].Value = model.Balance;
            ////parameters[25].Value = model.Credit;
            //parameters[20].Value = model.UserStatus;
            //parameters[21].Value = model.ExpiredDate;
            //parameters[22].Value = DateTime.Now;
            //parameters[23].Value = model.StampUserId;
            ////parameters[30].Value = model.UpdateCount;
            ////parameters[31].Value = model.ValidateStatus;
            ////parameters[32].Value = model.ValidateCode;
            //return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into UserInfo(");
            strSql.Append("UserId,NickName,RealityName,Integral,ParentId,RoleId,PswHash,PswSalt,PswFormat,PswExpiredDate,Province,City,Area,Address,QQ,EMail,TelPhone,Mobile,Birthday,IDcards,HeadImg,Sex,LoginIp,LoginTime,LastLoginIp,LastLoginTime,LastLoginGame,GameArea,LastLoginGameTime,AmountTotal,AmountInUse,Balance,Credit,UserStatus,UnFreezeTime,StampUserId,ExpiredDate,CreateTime,LastUpdateTime,UpdateCount,ValidateStatus,ValidateCode)");
            strSql.Append(" values (");
            strSql.Append("@UserId,@NickName,@RealityName,@Integral,@ParentId,@RoleId,@PswHash,@PswSalt,@PswFormat,@PswExpiredDate,@Province,@City,@Area,@Address,@QQ,@EMail,@TelPhone,@Mobile,@Birthday,@IDcards,@HeadImg,@Sex,@LoginIp,@LoginTime,@LastLoginIp,@LastLoginTime,@LastLoginGame,@GameArea,@LastLoginGameTime,@AmountTotal,@AmountInUse,@Balance,@Credit,@UserStatus,@UnFreezeTime,@StampUserId,@ExpiredDate,@CreateTime,@LastUpdateTime,@UpdateCount,@ValidateStatus,@ValidateCode)");
            SqlParameter[] parameters = {
					new SqlParameter("@UserId", SqlDbType.VarChar,20),
					new SqlParameter("@NickName", SqlDbType.NVarChar,20),
					new SqlParameter("@RealityName", SqlDbType.NVarChar,50),
					new SqlParameter("@Integral", SqlDbType.Int,4),
					new SqlParameter("@ParentId", SqlDbType.VarChar,20),
					new SqlParameter("@RoleId", SqlDbType.Int,4),
					new SqlParameter("@PswHash", SqlDbType.VarChar,100),
					new SqlParameter("@PswSalt", SqlDbType.VarChar,50),
					new SqlParameter("@PswFormat", SqlDbType.Int,4),
					new SqlParameter("@PswExpiredDate", SqlDbType.DateTime),
					new SqlParameter("@Province", SqlDbType.Int,4),
					new SqlParameter("@City", SqlDbType.Int,4),
					new SqlParameter("@Area", SqlDbType.Int,4),
					new SqlParameter("@Address", SqlDbType.NVarChar,50),
					new SqlParameter("@QQ", SqlDbType.VarChar,10),
					new SqlParameter("@EMail", SqlDbType.VarChar,100),
					new SqlParameter("@TelPhone", SqlDbType.VarChar,100),
					new SqlParameter("@Mobile", SqlDbType.VarChar,100),
					new SqlParameter("@Birthday", SqlDbType.DateTime),
					new SqlParameter("@IDcards", SqlDbType.VarChar,18),
					new SqlParameter("@HeadImg", SqlDbType.VarChar,150),
					new SqlParameter("@Sex", SqlDbType.Int,4),
					new SqlParameter("@LoginIp", SqlDbType.VarChar,15),
					new SqlParameter("@LoginTime", SqlDbType.DateTime),
					new SqlParameter("@LastLoginIp", SqlDbType.VarChar,15),
					new SqlParameter("@LastLoginTime", SqlDbType.DateTime),
					new SqlParameter("@LastLoginGame", SqlDbType.Int,4),
					new SqlParameter("@GameArea", SqlDbType.Int,4),
					new SqlParameter("@LastLoginGameTime", SqlDbType.DateTime),
					new SqlParameter("@AmountTotal", SqlDbType.Money,8),
					new SqlParameter("@AmountInUse", SqlDbType.Money,8),
					new SqlParameter("@Balance", SqlDbType.Money,8),
					new SqlParameter("@Credit", SqlDbType.Money,8),
					new SqlParameter("@UserStatus", SqlDbType.Int,4),
					new SqlParameter("@UnFreezeTime", SqlDbType.DateTime),
					new SqlParameter("@StampUserId", SqlDbType.VarChar,20),
					new SqlParameter("@ExpiredDate", SqlDbType.DateTime),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),
					new SqlParameter("@LastUpdateTime", SqlDbType.DateTime),
					new SqlParameter("@UpdateCount", SqlDbType.Int,4),
					new SqlParameter("@ValidateStatus", SqlDbType.Int,4),
					new SqlParameter("@ValidateCode", SqlDbType.Char,6)};
            //一下确保不出 SqlDateTime 溢出。必须介于 1/1/1753 12:00:00 AM 和 12/31/9999 11:59:59 PM 之间
            DateTime date = DateTime.Parse("0001-1-1 00:00:00");
            DateTime defaultdate = DateTime.Parse("1900-1-1");
            if (model.PswExpiredDate.HasValue == false) model.PswExpiredDate = defaultdate;
            if (model.LoginTime.HasValue == false) model.LoginTime = defaultdate;
            if (model.LastLoginTime.HasValue == false) model.LastLoginTime = defaultdate;
            if (model.LastLoginGameTime.HasValue == false) model.LastLoginGameTime = defaultdate;
            if (model.UnFreezeTime.HasValue == false) model.UnFreezeTime = defaultdate;
            if (model.ExpiredDate.HasValue == false) model.ExpiredDate = defaultdate;
            if ((model.CreateTime - date).TotalDays == 0.0) model.CreateTime = DateTime.Now;
            if ((model.LastUpdateTime - date).TotalDays == 0.0) model.LastUpdateTime = DateTime.Now;
            if (model.Birthday.HasValue == false) model.Birthday = defaultdate;
            parameters[0].Value = model.UserId;
            parameters[1].Value = model.NickName;
            parameters[2].Value = model.RealityName;
            parameters[3].Value = model.Integral;
            parameters[4].Value = model.ParentId;
            parameters[5].Value = model.RoleId;
            parameters[6].Value = model.PswHash;
            parameters[7].Value = model.PswSalt;
            parameters[8].Value = model.PswFormat;
            parameters[9].Value = model.PswExpiredDate;
            parameters[10].Value = model.Province;
            parameters[11].Value = model.City;
            parameters[12].Value = model.Area;
            parameters[13].Value = model.Address;
            parameters[14].Value = model.QQ;
            parameters[15].Value = model.EMail;
            parameters[16].Value = model.TelPhone;
            parameters[17].Value = model.Mobile;
            parameters[18].Value = model.Birthday;
            parameters[19].Value = model.IDcards;
            parameters[20].Value = model.HeadImg;
            parameters[21].Value = model.Sex;
            parameters[22].Value = model.LoginIp;
            parameters[23].Value = model.LoginTime;
            parameters[24].Value = model.LastLoginIp;
            parameters[25].Value = model.LastLoginTime;
            parameters[26].Value = model.LastLoginGame;
            parameters[27].Value = model.GameArea;
            parameters[28].Value = model.LastLoginGameTime;
            parameters[29].Value = model.AmountTotal;
            parameters[30].Value = model.AmountInUse;
            parameters[31].Value = model.Balance;
            parameters[32].Value = model.Credit;
            parameters[33].Value = model.UserStatus;
            parameters[34].Value = model.UnFreezeTime;
            parameters[35].Value = model.StampUserId;
            parameters[36].Value = model.ExpiredDate;
            parameters[37].Value = model.CreateTime;
            parameters[38].Value = model.LastUpdateTime;
            parameters[39].Value = model.UpdateCount;
            parameters[40].Value = model.ValidateStatus;
            parameters[41].Value = model.ValidateCode;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add2(UserInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into UserInfo(");
            strSql.Append("UserId,NickName,RealityName,Integral,ParentId,RoleId,PswHash,PswSalt,PswFormat,Address,QQ,EMail,TelPhone,Mobile,IDcards,Sex,AmountTotal,AmountInUse,UserStatus,ExpiredDate,CreateTime,StampUserId");
            strSql.Append(") values (");
            strSql.Append("@UserId,@NickName,@RealityName,@Integral,@ParentId,@RoleId,@PswHash,@PswSalt,@PswFormat,@Address,@QQ,@EMail,@TelPhone,@Mobile,@IDcards,@Sex,@AmountTotal,@AmountInUse,@UserStatus,@ExpiredDate,@CreateTime,@StampUserId");
            strSql.Append(") ");
            SqlParameter[] parameters = {
			            new SqlParameter("@UserId", model.UserId) ,            
                        new SqlParameter("@NickName", model.NickName) ,            
                        new SqlParameter("@RealityName", model.RealityName) ,            
                        new SqlParameter("@Integral", model.Integral) ,            
                        new SqlParameter("@ParentId", model.ParentId) ,            
                        new SqlParameter("@RoleId", model.RoleId) ,            
                        new SqlParameter("@PswHash", model.PswHash) ,            
                        new SqlParameter("@PswSalt", model.PswSalt) ,            
                        new SqlParameter("@PswFormat",model.PswFormat) ,            
                        new SqlParameter("@Address", model.Address) ,            
                        new SqlParameter("@QQ", model.QQ) ,            
                        new SqlParameter("@EMail", model.EMail) ,            
                        new SqlParameter("@TelPhone", model.TelPhone) ,            
                        new SqlParameter("@Mobile", model.Mobile) ,            
                        new SqlParameter("@IDcards", model.IDcards) ,            
                        new SqlParameter("@HeadImg", model.HeadImg) ,            
                        new SqlParameter("@Sex", model.Sex) ,            
                        new SqlParameter("@AmountTotal", model.AmountTotal) ,            
                        new SqlParameter("@AmountInUse", model.AmountInUse) ,            
                        new SqlParameter("@UserStatus", model.UserStatus) ,            
                        new SqlParameter("@ExpiredDate", model.ExpiredDate) ,            
                        new SqlParameter("@CreateTime", DateTime.Now),             
                        new SqlParameter("@StampUserId", model.StampUserId)                      
            };
            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }
        #endregion
        #region 更新记录
        /// <summary>
        /// 如果不进行完整复制，则必须先加载实体，然后修改其中若干值，再进行更新操作
        /// </summary>
        public bool Update(UserInfo model)
        {
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("update UserInfo set ");
            //strSql.Append(" NickName = @NickName , ");
            //strSql.Append(" RealityName = @RealityName , ");
            //strSql.Append(" Integral = @Integral , ");
            //strSql.Append(" RoleId = @RoleId , ");
            //strSql.Append(" Address = @Address , ");
            //strSql.Append(" QQ = @QQ , ");
            //strSql.Append(" EMail = @EMail , ");
            //strSql.Append(" TelPhone = @TelPhone , ");
            //strSql.Append(" Mobile = @Mobile , ");
            //strSql.Append(" Birthday = @Birthday , ");
            //strSql.Append(" IDcards = @IDcards , ");
            //strSql.Append(" Sex = @Sex , ");
            //strSql.Append(" AmountTotal = @AmountTotal , ");
            //strSql.Append(" AmountInUse = @AmountInUse , ");
            //strSql.Append(" UserStatus = @UserStatus , ");
            //strSql.Append(" StampUserId = @StampUserId , ");
            //strSql.Append(" ExpiredDate = @ExpiredDate , ");
            //strSql.Append(" LastUpdateTime =getdate(),ParentId=@ParentId,LoginIp=@LoginIp,LoginTime=@LoginTime,LastLoginIp=@LastLoginIp,LastLoginTime=getdate()");
            //strSql.Append(" ,PswHash = @PswHash");
            //strSql.Append(" ,PswSalt = @PswSalt");
            //strSql.Append(" ,PswFormat = @PswFormat");
            //strSql.Append(" where UserId=@UserId");
            ////风山渐
            ////如果该用户拥有子账号，那么同时更改该用户下子账号的审核状态。
            ////但是由于账号没有审核通过是不可以创建子账号的,因此逻辑上不存在更改子账号状态的理由
            ////strSql.Append(" ;update UserInfo set UserStatus = @UserStatus where ParentId=@UserId and RoleId in (7,8,9,10,11,12)");
            //SqlParameter[] parameters = {
            //            new SqlParameter("@UserId", SqlDbType.NVarChar,20) ,            
            //            new SqlParameter("@NickName", SqlDbType.NVarChar,20) ,            
            //            new SqlParameter("@RealityName", SqlDbType.NVarChar,10) ,            
            //            new SqlParameter("@Integral", SqlDbType.Int,4) ,            
            //            new SqlParameter("@RoleId", SqlDbType.Int,4) ,            
            //            new SqlParameter("@Address", SqlDbType.NVarChar,50) ,            
            //            new SqlParameter("@QQ", SqlDbType.VarChar,10) ,            
            //            new SqlParameter("@EMail", SqlDbType.VarChar,100) ,            
            //            new SqlParameter("@TelPhone", SqlDbType.VarChar,100) ,            
            //            new SqlParameter("@Mobile", SqlDbType.VarChar,100) ,            
            //            new SqlParameter("@Birthday", SqlDbType.DateTime) ,            
            //            new SqlParameter("@IDcards", SqlDbType.VarChar,18) ,            
            //            new SqlParameter("@Sex", SqlDbType.Int,4) ,            
            //            new SqlParameter("@AmountTotal", SqlDbType.Money,8) ,            
            //            new SqlParameter("@AmountInUse", SqlDbType.Money,8) ,            
            //            new SqlParameter("@UserStatus", SqlDbType.Int,4) ,            
            //            new SqlParameter("@StampUserId", SqlDbType.VarChar,20) ,            
            //            new SqlParameter("@ExpiredDate", SqlDbType.DateTime)  
            //            ,new SqlParameter("@ParentId", SqlDbType.NVarChar,20)
            //            ,new SqlParameter("@LoginIp", SqlDbType.VarChar,15)
            //            ,new SqlParameter("@LoginTime", SqlDbType.DateTime)   
            //            ,new SqlParameter("@LastLoginIp", SqlDbType.VarChar,15) 
            //            ,new SqlParameter("@PswHash", SqlDbType.VarChar,50)  {Value=model.PswHash}           
            //            ,new SqlParameter("@PswSalt", SqlDbType.VarChar,50)  {Value=model.PswSalt}          
            //            ,new SqlParameter("@PswFormat", SqlDbType.Int,4)  {Value=model.PswFormat}
            //};
            //parameters[0].Value = model.UserId;
            //parameters[1].Value = model.NickName;
            //parameters[2].Value = model.RealityName;
            //parameters[3].Value = model.Integral;
            //parameters[4].Value = model.RoleId;
            //parameters[5].Value = model.Address;
            //parameters[6].Value = model.QQ;
            //parameters[7].Value = model.EMail;
            //parameters[8].Value = model.TelPhone;
            //parameters[9].Value = model.Mobile;
            //parameters[10].Value = model.Birthday;
            //parameters[11].Value = model.IDcards;
            //parameters[12].Value = model.Sex;
            //parameters[13].Value = model.AmountTotal;
            //parameters[14].Value = model.AmountInUse;
            //parameters[15].Value = model.UserStatus;
            //parameters[16].Value = model.StampUserId;
            //parameters[17].Value = model.ExpiredDate;
            //parameters[18].Value = model.ParentId;
            //parameters[19].Value = model.LoginIp;
            //parameters[20].Value = model.LoginTime;
            //parameters[21].Value = model.LastLoginIp;
            //int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            //if (rows > 0)
            //{
            //    return true;
            //}
            //else
            //{
            //    return false;
            //}
            //以上sql缺少字段会导致省，市，区等信息更新不到
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update UserInfo set ");
            strSql.Append("NickName=@NickName,");
            strSql.Append("RealityName=@RealityName,");
            strSql.Append("Integral=@Integral,");
            strSql.Append("ParentId=@ParentId,");
            strSql.Append("RoleId=@RoleId,");
            strSql.Append("PswHash=@PswHash,");
            strSql.Append("PswSalt=@PswSalt,");
            strSql.Append("PswFormat=@PswFormat,");
            strSql.Append("PswExpiredDate=@PswExpiredDate,");
            strSql.Append("Province=@Province,");
            strSql.Append("City=@City,");
            strSql.Append("Area=@Area,");
            strSql.Append("Address=@Address,");
            strSql.Append("QQ=@QQ,");
            strSql.Append("EMail=@EMail,");
            strSql.Append("TelPhone=@TelPhone,");
            strSql.Append("Mobile=@Mobile,");
            strSql.Append("Birthday=@Birthday,");
            strSql.Append("IDcards=@IDcards,");
            strSql.Append("HeadImg=@HeadImg,");
            strSql.Append("Sex=@Sex,");
            strSql.Append("LoginIp=@LoginIp,");
            strSql.Append("LoginTime=@LoginTime,");
            strSql.Append("LastLoginIp=@LastLoginIp,");
            strSql.Append("LastLoginTime=@LastLoginTime,");
            strSql.Append("LastLoginGame=@LastLoginGame,");
            strSql.Append("GameArea=@GameArea,");
            strSql.Append("LastLoginGameTime=@LastLoginGameTime,");
            strSql.Append("AmountTotal=@AmountTotal,");
            strSql.Append("AmountInUse=@AmountInUse,");
            strSql.Append("Balance=@Balance,");
            strSql.Append("Credit=@Credit,");
            strSql.Append("UserStatus=@UserStatus,");
            strSql.Append("UnFreezeTime=@UnFreezeTime,");
            strSql.Append("StampUserId=@StampUserId,");
            strSql.Append("ExpiredDate=@ExpiredDate,");
            strSql.Append("CreateTime=@CreateTime,");
            strSql.Append("LastUpdateTime=@LastUpdateTime,");
            strSql.Append("UpdateCount=@UpdateCount,");
            strSql.Append("ValidateStatus=@ValidateStatus,");
            strSql.Append("ValidateCode=@ValidateCode");
            strSql.Append(" where UserId=@UserId ");
            SqlParameter[] parameters = {
					new SqlParameter("@NickName", SqlDbType.NVarChar,20),
					new SqlParameter("@RealityName", SqlDbType.NVarChar,50),
					new SqlParameter("@Integral", SqlDbType.Int,4),
					new SqlParameter("@ParentId", SqlDbType.VarChar,20),
					new SqlParameter("@RoleId", SqlDbType.Int,4),
					new SqlParameter("@PswHash", SqlDbType.VarChar,100),
					new SqlParameter("@PswSalt", SqlDbType.VarChar,50),
					new SqlParameter("@PswFormat", SqlDbType.Int,4),
					new SqlParameter("@PswExpiredDate", SqlDbType.DateTime),
					new SqlParameter("@Province", SqlDbType.Int,4),
					new SqlParameter("@City", SqlDbType.Int,4),
					new SqlParameter("@Area", SqlDbType.Int,4),
					new SqlParameter("@Address", SqlDbType.NVarChar,50),
					new SqlParameter("@QQ", SqlDbType.VarChar,10),
					new SqlParameter("@EMail", SqlDbType.VarChar,100),
					new SqlParameter("@TelPhone", SqlDbType.VarChar,100),
					new SqlParameter("@Mobile", SqlDbType.VarChar,100),
					new SqlParameter("@Birthday", SqlDbType.DateTime),
					new SqlParameter("@IDcards", SqlDbType.VarChar,18),
					new SqlParameter("@HeadImg", SqlDbType.VarChar,150),
					new SqlParameter("@Sex", SqlDbType.Int,4),
					new SqlParameter("@LoginIp", SqlDbType.VarChar,15),
					new SqlParameter("@LoginTime", SqlDbType.DateTime),
					new SqlParameter("@LastLoginIp", SqlDbType.VarChar,15),
					new SqlParameter("@LastLoginTime", SqlDbType.DateTime),
					new SqlParameter("@LastLoginGame", SqlDbType.Int,4),
					new SqlParameter("@GameArea", SqlDbType.Int,4),
					new SqlParameter("@LastLoginGameTime", SqlDbType.DateTime),
					new SqlParameter("@AmountTotal", SqlDbType.Money,8),
					new SqlParameter("@AmountInUse", SqlDbType.Money,8),
					new SqlParameter("@Balance", SqlDbType.Money,8),
					new SqlParameter("@Credit", SqlDbType.Money,8),
					new SqlParameter("@UserStatus", SqlDbType.Int,4),
					new SqlParameter("@UnFreezeTime", SqlDbType.DateTime),
					new SqlParameter("@StampUserId", SqlDbType.VarChar,20),
					new SqlParameter("@ExpiredDate", SqlDbType.DateTime),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),
					new SqlParameter("@LastUpdateTime", SqlDbType.DateTime),
					new SqlParameter("@UpdateCount", SqlDbType.Int,4),
					new SqlParameter("@ValidateStatus", SqlDbType.Int,4),
					new SqlParameter("@ValidateCode", SqlDbType.Char,6),					
					new SqlParameter("@UserId", SqlDbType.VarChar,20)};
            parameters[0].Value = model.NickName;
            parameters[1].Value = model.RealityName;
            parameters[2].Value = model.Integral;
            parameters[3].Value = model.ParentId;
            parameters[4].Value = model.RoleId;
            parameters[5].Value = model.PswHash;
            parameters[6].Value = model.PswSalt;
            parameters[7].Value = model.PswFormat;
            parameters[8].Value = model.PswExpiredDate;
            parameters[9].Value = model.Province;
            parameters[10].Value = model.City;
            parameters[11].Value = model.Area;
            parameters[12].Value = model.Address;
            parameters[13].Value = model.QQ;
            parameters[14].Value = model.EMail;
            parameters[15].Value = model.TelPhone;
            parameters[16].Value = model.Mobile;
            parameters[17].Value = model.Birthday;
            parameters[18].Value = model.IDcards;
            parameters[19].Value = model.HeadImg;
            parameters[20].Value = model.Sex;
            parameters[21].Value = model.LoginIp;
            parameters[22].Value = model.LoginTime;
            parameters[23].Value = model.LastLoginIp;
            parameters[24].Value = model.LastLoginTime;
            parameters[25].Value = model.LastLoginGame;
            parameters[26].Value = model.GameArea;
            parameters[27].Value = model.LastLoginGameTime;
            parameters[28].Value = model.AmountTotal;
            parameters[29].Value = model.AmountInUse;
            parameters[30].Value = model.Balance;
            parameters[31].Value = model.Credit;
            parameters[32].Value = model.UserStatus;
            parameters[33].Value = model.UnFreezeTime;
            parameters[34].Value = model.StampUserId;
            parameters[35].Value = model.ExpiredDate;
            parameters[36].Value = model.CreateTime;
            parameters[37].Value = model.LastUpdateTime;
            parameters[38].Value = model.UpdateCount;
            parameters[39].Value = model.ValidateStatus;
            parameters[40].Value = model.ValidateCode;
            parameters[41].Value = model.UserId;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public bool UpdatePart(List<string> fields, List<object> values)
        {
            if (fields == null || fields.Count == 0 || values == null || values.Count == 0)
            {
                return false;
            }
            StringBuilder strSql = new StringBuilder();
            StringBuilder where = new StringBuilder();
            strSql.Append(" update  UserInfo  set  ");
            where.Append("  where 1=1  ");
            List<SqlParameter> parm = new List<SqlParameter>();
            int aa = 0;
            for (int i = 0; i < fields.Count; i++)
            {
                if (fields[i].StartsWith("where_"))
                {
                    fields[i] = fields[i].Substring(6);
                    if (fields[i].StartsWith("like_"))
                    {
                        where.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                    }
                    else if (fields[i].StartsWith("not_"))
                    {
                        where.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                    }
                    else if (fields[i].StartsWith("lt_"))
                    {
                        where.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                    }
                    else if (fields[i].StartsWith("gt_"))
                    {
                        where.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                    }
                    else
                    {
                        where.Append("  and  " + fields[i] + "=@" + fields[i] + "  ");
                    }
                }
                else
                {
                    if (aa > 0)
                    {
                        strSql.Append("  ,  ");
                    }
                    strSql.Append("  " + fields[i] + "=@" + fields[i] + " ");
                    aa++;
                }
                parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
            }
            strSql.Append(where);
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parm.ToArray());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion
        /// <summary>
        /// 设置指定用户的状态
        /// </summary>
        /// <param name="userId">用户名</param>
        /// <param name="status">状态编号</param>
        /// <returns></returns>
        public bool UpdateUserStatus(string userId, int status)
        {
            return DbHelperSQL.ExecuteSql("update UserInfo set UserStatus=@UserStatus where UserId=@UserId", new SqlParameter("@UserId", userId), new SqlParameter("@UserStatus", status)) > 0;
        }
        /// <summary>
        /// 更新指定用户的父级用户
        /// </summary>
        /// <param name="userId">用户名</param>
        /// <param name="parentId">父级用户名</param>
        /// <returns></returns>
        public bool UpdateParentId(string userId, string parentId)
        {
            return DbHelperSQL.ExecuteSql("update UserInfo set ParentId=@ParentId where UserId=@UserId", new SqlParameter("@ParentId", parentId), new SqlParameter("@UserId", userId)) > 0;
        }
        /// <summary>
        /// 退出网吧
        /// </summary>
        /// <param name="userId">指定的用户名</param>
        /// <param name="oldParentId">旧父级id</param>
        /// <returns></returns>
        public bool ExitCafes(string userId, string oldParentId)
        {
            return DbHelperSQL.ExecuteSql(@"update UserInfo set ParentId='Root',UserStatus=UserStatus^64,UnFreezeTime=@UnFreezeTime where UserId=@UserId
;update ShareRateSet set ActShareRate=1,ShareRate=0 where UserId=@UserId
;insert CmShortMessage (ReceiverIds,SenderId,MesId,PriorityId,MContent,SendTime) values('Root',@UserId,7,2,@UserId +' 推广员已确认退出绑定网吧' + @OldParentId + '，现归入平台，此用户状态已设置为审核中，请在下次结算后，调整此用户占成设置。[系统自动生成信息]',GETDATE())
;insert CmUserMessage (MessageId,ReceiverId,IsReaded,IsConfirm) values (SCOPE_IDENTITY(),'Root',0,0)
"
                , new SqlParameter("@UnFreezeTime", DateTime.Now.AddDays(30d))
                , new SqlParameter("@UserId", userId)
                , new SqlParameter("@OldParentId", oldParentId)) > 0;
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string UserId, string operatUser)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@UserId", SqlDbType.VarChar,20){Value=UserId}
                    ,new SqlParameter("@Operator", SqlDbType.VarChar,20) {Value=operatUser}                   
                    };
            return DbHelperSQL.GetIntByPro("[DeleteUser]", parameters) == 1;
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(List<string> UserId)
        {
            if (UserId == null || UserId.Count < 1)
            {
                return false;
            }
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from UserInfo ");
            strSql.Append(" where UserId in (@UserId)");
            StringBuilder sbId = new StringBuilder();
            foreach (var id in UserId)
            {
                sbId.AppendFormat("{0},", id);
            }
            sbId.Remove(sbId.Length - 1, 1);
            SqlParameter[] parameters = {
					new SqlParameter("@UserId", SqlDbType.VarChar,20)			};
            parameters[0].Value = sbId.ToString();
            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public UserInfo GetModel(string UserId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append("  from UserInfo ");
            strSql.Append(" where UserId=@UserId ");
            SqlParameter[] parameters = {
					new SqlParameter("@UserId", SqlDbType.VarChar,20)			};
            parameters[0].Value = UserId;
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return createModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        private UserInfo createModel(DataRow dr)
        {
            UserInfo model = new UserInfo();
            //model.SecuritySet = ConvertUtil.ToString(dr["SecuritySet"]);
            model.seqno = int.Parse(dr["seqno"].ToString());
            model.UserId = dr["UserId"].ToString();
            model.NickName = dr["NickName"].ToString();
            model.RealityName = dr["RealityName"].ToString();
            if (dr["Integral"].ToString() != "")
            {
                model.Integral = int.Parse(dr["Integral"].ToString());
            }
            model.ParentId = dr["ParentId"].ToString();
            if (dr["RoleId"].ToString() != "")
            {
                model.RoleId = int.Parse(dr["RoleId"].ToString());
            }
            model.PswHash = dr["PswHash"].ToString();
            model.PswSalt = dr["PswSalt"].ToString();
            if (dr["PswFormat"].ToString() != "")
            {
                model.PswFormat = int.Parse(dr["PswFormat"].ToString());
            }
            if (dr["PswExpiredDate"].ToString() != "")
            {
                model.PswExpiredDate = DateTime.Parse(dr["PswExpiredDate"].ToString());
            }
            model.Address = dr["Address"].ToString();
            model.QQ = dr["QQ"].ToString();
            model.EMail = dr["EMail"].ToString();
            model.TelPhone = dr["TelPhone"].ToString();
            model.Mobile = dr["Mobile"].ToString();
            if (dr["Birthday"].ToString() != "")
            {
                model.Birthday = DateTime.Parse(dr["Birthday"].ToString());
            }
            model.IDcards = dr["IDcards"].ToString();
            model.HeadImg = dr["HeadImg"].ToString();
            if (dr["Sex"].ToString() != "")
            {
                model.Sex = int.Parse(dr["Sex"].ToString());
            }
            model.LoginIp = dr["LoginIp"].ToString();
            if (dr["LoginTime"].ToString() != "")
            {
                model.LoginTime = DateTime.Parse(dr["LoginTime"].ToString());
            }
            model.LastLoginIp = dr["LastLoginIp"].ToString();
            if (dr["LastLoginTime"].ToString() != "")
            {
                model.LastLoginTime = DateTime.Parse(dr["LastLoginTime"].ToString());
            }
            if (dr["LastLoginGame"].ToString() != "")
            {
                model.LastLoginGame = int.Parse(dr["LastLoginGame"].ToString());
            }
            if (dr["GameArea"].ToString() != "")
            {
                model.GameArea = int.Parse(dr["GameArea"].ToString());
            }
            if (dr["LastLoginGameTime"].ToString() != "")
            {
                model.LastLoginGameTime = DateTime.Parse(dr["LastLoginGameTime"].ToString());
            }
            if (dr["AmountTotal"].ToString() != "")
            {
                model.AmountTotal = decimal.Parse(dr["AmountTotal"].ToString());
            }
            if (dr["AmountInUse"].ToString() != "")
            {
                model.AmountInUse = decimal.Parse(dr["AmountInUse"].ToString());
            }
            if (dr["Balance"].ToString() != "")
            {
                model.Balance = decimal.Parse(dr["Balance"].ToString());
            }
            if (dr["Credit"].ToString() != "")
            {
                model.Credit = decimal.Parse(dr["Credit"].ToString());
            }
            if (dr["UserStatus"].ToString() != "")
            {
                model.UserStatus = int.Parse(dr["UserStatus"].ToString());
            }
            if (dr["UnFreezeTime"].ToString() != "")
            {
                model.UnFreezeTime = DateTime.Parse(dr["UnFreezeTime"].ToString());
            }
            model.StampUserId = dr["StampUserId"].ToString();
            if (dr["ExpiredDate"].ToString() != "")
            {
                model.ExpiredDate = DateTime.Parse(dr["ExpiredDate"].ToString());
            }
            if (dr["CreateTime"].ToString() != "")
            {
                model.CreateTime = DateTime.Parse(dr["CreateTime"].ToString());
            }
            if (dr["LastUpdateTime"].ToString() != "")
            {
                model.LastUpdateTime = DateTime.Parse(dr["LastUpdateTime"].ToString());
            }
            if (dr["UpdateCount"].ToString() != "")
            {
                model.UpdateCount = int.Parse(dr["UpdateCount"].ToString());
            }
            if (dr["ValidateStatus"].ToString() != "")
            {
                model.ValidateStatus = int.Parse(dr["ValidateStatus"].ToString());
            }
            model.ValidateCode = dr["ValidateCode"].ToString();
            model.Province = ConvertUtil.ToInt(dr["Province"]);
            model.City = ConvertUtil.ToInt(dr["City"]);
            model.Area = ConvertUtil.ToInt(dr["Area"]);
            return model;
        }
        /// <summary>
        /// 获取审核中的列表
        /// </summary>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public DataTable GetAuditingList(string parentId)
        {
            return DbHelperSQL.GetDataTable(@"select a.*,b.DisplayName as RoleName from UserInfo as a left join RoleInfo as b on a.RoleId=b.RoleID where ParentId=@ParentId and UserStatus&64=64"
                , new SqlParameter("@ParentId", parentId));
        }
        public string CreateCountSql(List<string> fields, List<object> values)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from UserInfo as a left join RoleInfo as b on a.RoleId=b.RoleID  where UserStatus&64=64 ");
            List<SqlParameter> parm = new List<SqlParameter>(); if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    if (fields[i].ToLower().StartsWith("like_"))
                    {
                        strSql.Append(" AND CHARINDEX(@" + fields[i] + ",a." + fields[i].Split('_')[1] + ")>0 ");
                    }
                    else if (fields[i].ToLower().StartsWith("start_"))
                    {
                        strSql.Append(" AND DATEDIFF(day,@" + fields[i] + ",a." + fields[i].Split('_')[1] + ")>=0");
                    }
                    else if (fields[i].ToLower().StartsWith("end_"))
                    {
                        strSql.Append(" AND DATEDIFF(day,@" + fields[i] + ",a." + fields[i].Split('_')[1] + ")<=0");
                    }
                    else if (fields[i].ToLower().StartsWith("and_"))
                    {
                        strSql.Append(" AND (a." + fields[i].Split('_')[1] + "& @" + fields[i] + ")=@" + fields[i] + " ");
                    }
                    else
                    {
                        strSql.Append(" AND a." + fields[i] + "=@" + fields[i] + " ");
                    }
                    parm.Add(new SqlParameter("@" + fields[i], values[i]));
                }
            }
            return strSql.ToString();
        }
        /// <summary>
        /// 返回指定父级用户的下两级的待审核列表
        /// </summary>
        /// <param name="parentId">指定的父级用户</param>
        /// <param name="orderby">排序语句，BankInfos为a.；UserInfo为b.，可以加上desc，比如a.LastUpdateTime desc，留空使用默认a.LastUpdateTime desc</param>
        /// <param name="startIndex">开始行号</param>
        /// <param name="endIndex">结束行号</param>
        /// <returns></returns>
        public DataSet GetAuditingListByPage2_BindCafe(string parentId, string orderby, int startIndex, int endIndex)
        {
            if (string.IsNullOrEmpty(orderby))
            {
                orderby = "a.LastUpdateTime desc";
            }
            return DbHelperSQL.Query(@"--查询两个层次的列表(待审核用户列表)
select * from (
select ROW_NUMBER() over(order by a.LastUpdateTime desc) as Row
,a.*,b.DisplayName as RoleName from UserInfo as a 
left join RoleInfo as b on a.RoleId=b.RoleID
where a.UserStatus&2048=2048 and (a.ParentId=@ParentId or a.ParentId in (select UserId from UserInfo where ParentId=@ParentId and RoleId!=4))
) as c
where c.Row between @StartIndex and @EndIndex
--计算总行数
select COUNT(a.UserId) from UserInfo as a 
where a.UserStatus&64=64 and (a.ParentId=@ParentId or a.ParentId in (select UserId from UserInfo where ParentId=@ParentId and RoleId!=4))"
                .FormatWith(orderby)
            , new SqlParameter("@ParentId", SqlDbType.VarChar, 20) { Value = parentId }
            , new SqlParameter("@StartIndex", SqlDbType.Int) { Value = startIndex }
            , new SqlParameter("@EndIndex", SqlDbType.Int) { Value = endIndex }
            );
        }
        /// <summary>
        /// 返回指定父级用户的下1级的待审核列表
        /// </summary>
        /// <param name="targetUser">指定的待审核的用户</param>
        /// <param name="parentId">指定的父级用户</param>
        /// <param name="examiner">指定的审核跟进用户</param>
        /// <param name="orderby">排序语句，RoleInfo b,UserInfo a,UserExp d，留空使用默认a.LastUpdateTime desc</param>
        /// <param name="startIndex">开始行号</param>
        /// <param name="endIndex">结束行号</param>
        /// <returns></returns>
        public DataTable GetExamineListByPage1(string targetUser, string parentId, string examiner, string orderby, int startIndex, int endIndex)
        {
            return DbHelperSQL.GetDataTableByPro("[QueryExamineList1]"
            , new SqlParameter("@TargetUser", SqlDbType.VarChar, 20) { Value = targetUser }
            , new SqlParameter("@ParentId", SqlDbType.VarChar, 20) { Value = parentId }
            , new SqlParameter("@Examiner", SqlDbType.VarChar, 20) { Value = examiner }
            , new SqlParameter("@OrderBy", SqlDbType.VarChar, 20) { Value = orderby }
            , new SqlParameter("@StartIndex", SqlDbType.Int) { Value = startIndex }
            , new SqlParameter("@EndIndex", SqlDbType.Int) { Value = endIndex }
            );
        }
        /// <summary>
        /// 返回指定父级用户的下1级的待审核列表行数
        /// </summary>
        /// <param name="targetUser">指定的待审核的用户</param>
        /// <param name="parentId">指定的父级用户</param>
        /// <param name="examiner">指定的审核跟进用户</param>
        /// <returns></returns>
        public int GetExamineListCount1(string targetUser, string parentId, string examiner)
        {
            return DbHelperSQL.GetIntByPro_Query("[QueryExamineListCount1]"
            , new SqlParameter("@TargetUser", SqlDbType.VarChar, 20) { Value = targetUser }
            , new SqlParameter("@ParentId", SqlDbType.VarChar, 20) { Value = parentId }
            , new SqlParameter("@Examiner", SqlDbType.VarChar, 20) { Value = examiner }
            );
        }
        #region root审核列表查询
        /// <summary>
        /// 返回指定父级用户root的待审核列表
        /// </summary>
        /// <param name="targetUser">指定的待审核的用户</param>
        /// <param name="parentId">指定的父级用户</param>
        /// <param name="examiner">指定的审核跟进用户</param>
        /// <param name="orderby">排序语句，RoleInfo b,UserInfo a,UserExp d，留空使用默认a.LastUpdateTime desc</param>
        /// <param name="startIndex">开始行号</param>
        /// <param name="endIndex">结束行号</param>
        /// <returns></returns>
        public DataTable GetExamineListByPageRoot(string targetUser, string parentId, string examiner, string orderby, int startIndex, int endIndex)
        {
            return DbHelperSQL.GetDataTableByPro("[QueryExamineListRoot]"
            , new SqlParameter("@TargetUser", SqlDbType.VarChar, 20) { Value = targetUser }
            , new SqlParameter("@ParentId", SqlDbType.VarChar, 20) { Value = parentId }
            , new SqlParameter("@Examiner", SqlDbType.VarChar, 20) { Value = examiner }
            , new SqlParameter("@OrderBy", SqlDbType.VarChar, 20) { Value = orderby }
            , new SqlParameter("@StartIndex", SqlDbType.Int) { Value = startIndex }
            , new SqlParameter("@EndIndex", SqlDbType.Int) { Value = endIndex }
            );
        }
        /// <summary>
        /// 返回指定父级用户root的待审核列表行数
        /// </summary>
        /// <param name="targetUser">指定的待审核的用户</param>
        /// <param name="parentId">指定的父级用户</param>
        /// <param name="examiner">指定的审核跟进用户</param>
        /// <returns></returns>
        public int GetExamineListCountRoot(string targetUser, string parentId, string examiner)
        {
            return DbHelperSQL.GetIntByPro_Query("[QueryExamineListCountRoot]"
            , new SqlParameter("@TargetUser", SqlDbType.VarChar, 20) { Value = targetUser }
            , new SqlParameter("@ParentId", SqlDbType.VarChar, 20) { Value = parentId }
            , new SqlParameter("@Examiner", SqlDbType.VarChar, 20) { Value = examiner }
            );
        }
        #endregion
        #region root审核列表查询
        /// <summary>
        /// 返回指定父级用户的下2级的待审核列表
        /// </summary>
        /// <param name="targetUser">指定的待审核的用户</param>
        /// <param name="parentId">指定的父级用户</param>
        /// <param name="examiner">指定的审核跟进用户</param>
        /// <param name="orderby">排序语句，RoleInfo b,UserInfo a,UserExp d，留空使用默认a.LastUpdateTime desc</param>
        /// <param name="startIndex">开始行号</param>
        /// <param name="endIndex">结束行号</param>
        /// <returns></returns>
        public DataTable GetExamineListByPage2(string targetUser, string parentId, string examiner, string orderby, int startIndex, int endIndex)
        {
            return DbHelperSQL.GetDataTableByPro("[QueryExamineList2]"
            , new SqlParameter("@TargetUser", SqlDbType.VarChar, 20) { Value = targetUser }
            , new SqlParameter("@ParentId", SqlDbType.VarChar, 20) { Value = parentId }
            , new SqlParameter("@Examiner", SqlDbType.VarChar, 20) { Value = examiner }
            , new SqlParameter("@OrderBy", SqlDbType.VarChar, 20) { Value = orderby }
            , new SqlParameter("@StartIndex", SqlDbType.Int) { Value = startIndex }
            , new SqlParameter("@EndIndex", SqlDbType.Int) { Value = endIndex }
            );
        }
        /// <summary>
        /// 返回指定父级用户的下2级的待审核列表行数
        /// </summary>
        /// <param name="targetUser">指定的待审核的用户</param>
        /// <param name="parentId">指定的父级用户</param>
        /// <param name="examiner">指定的审核跟进用户</param>
        /// <returns></returns>
        public int GetExamineListCount2(string targetUser, string parentId, string examiner)
        {
            return DbHelperSQL.GetIntByPro_Query("[QueryExamineListCount2]"
            , new SqlParameter("@TargetUser", SqlDbType.VarChar, 20) { Value = targetUser }
            , new SqlParameter("@ParentId", SqlDbType.VarChar, 20) { Value = parentId }
            , new SqlParameter("@Examiner", SqlDbType.VarChar, 20) { Value = examiner }
            );
        }
        #endregion
        /// <summary>
        /// 返回指定父级用户的下一级的待审核列表
        /// </summary>
        /// <param name="parentId">指定的父级用户</param>
        /// <param name="orderby">排序语句，BankInfos为a.；UserInfo为b.，可以加上desc，比如a.LastUpdateTime desc，留空使用默认a.LastUpdateTime desc</param>
        /// <param name="startIndex">开始行号</param>
        /// <param name="endIndex">结束行号</param>
        /// <returns></returns>
        public DataSet GetAuditingListByPage_BindCafe(string parentId, string orderby, int startIndex, int endIndex)
        {
            if (string.IsNullOrEmpty(orderby))
            {
                orderby = "a.LastUpdateTime desc";
            }
            return DbHelperSQL.Query(@"
select * from (
select ROW_NUMBER() over(order by a.LastUpdateTime desc) as Row
,a.*,b.DisplayName as RoleName from UserInfo as a 
left join RoleInfo as b on a.RoleId=b.RoleID
where a.UserStatus&2048=2048 and a.ParentId=@ParentId
) as c
where c.Row between @StartIndex and @EndIndex
--计算总行数
select COUNT(a.UserId) from UserInfo as a 
where a.UserStatus&64=64 and a.ParentId=@ParentId"
                .FormatWith(orderby)
            , new SqlParameter("@ParentId", SqlDbType.VarChar, 20) { Value = parentId }
            , new SqlParameter("@StartIndex", SqlDbType.Int) { Value = startIndex }
            , new SqlParameter("@EndIndex", SqlDbType.Int) { Value = endIndex }
            );
        }
        /// <summary>
        /// 返回指定父级用户的下两级的待审核列表 
        /// </summary>
        /// <param name="parentId">指定的父级用户</param>
        /// <param name="orderby">排序语句，BankInfos为a.；UserInfo为b.，可以加上desc，比如a.LastUpdateTime desc，留空使用默认a.LastUpdateTime desc</param>
        /// <param name="startIndex">开始行号</param>
        /// <param name="endIndex">结束行号</param>
        /// <returns></returns>
        public DataSet GetAuditingListByPage2(string parentId, string orderby, int startIndex, int endIndex)
        {
            if (string.IsNullOrEmpty(orderby))
            {
                orderby = "a.LastUpdateTime desc";
            }
            return DbHelperSQL.Query(@"--查询两个层次的列表(待审核用户列表)
select * from (
select ROW_NUMBER() over(order by a.LastUpdateTime desc) as Row
,a.*,b.DisplayName as RoleName from UserInfo as a 
left join RoleInfo as b on a.RoleId=b.RoleID
where a.UserStatus&64=64 and (a.ParentId=@ParentId or a.ParentId in (select UserId from UserInfo where ParentId=@ParentId and RoleId!=4))
) as c
where c.Row between @StartIndex and @EndIndex
--计算总行数
select COUNT(a.UserId) from UserInfo as a 
where a.UserStatus&64=64 and (a.ParentId=@ParentId or a.ParentId in (select UserId from UserInfo where ParentId=@ParentId and RoleId!=4))"
                .FormatWith(orderby)
            , new SqlParameter("@ParentId", SqlDbType.VarChar, 20) { Value = parentId }
            , new SqlParameter("@StartIndex", SqlDbType.Int) { Value = startIndex }
            , new SqlParameter("@EndIndex", SqlDbType.Int) { Value = endIndex }
            );
        }
        /// <summary>
        /// 返回指定父级用户的下一级的待审核列表
        /// </summary>
        /// <param name="parentId">指定的父级用户</param>
        /// <param name="orderby">排序语句，BankInfos为a.；UserInfo为b.，可以加上desc，比如a.LastUpdateTime desc，留空使用默认a.LastUpdateTime desc</param>
        /// <param name="startIndex">开始行号</param>
        /// <param name="endIndex">结束行号</param>
        /// <returns></returns>
        public DataSet GetAuditingListByPage(string parentId, string orderby, int startIndex, int endIndex)
        {
            if (string.IsNullOrEmpty(orderby))
            {
                orderby = "a.LastUpdateTime desc";
            }
            return DbHelperSQL.Query(@"
select * from (
select ROW_NUMBER() over(order by a.LastUpdateTime desc) as Row
,a.*,b.DisplayName as RoleName from UserInfo as a 
left join RoleInfo as b on a.RoleId=b.RoleID
where a.UserStatus&64=64 and a.ParentId=@ParentId
) as c
where c.Row between @StartIndex and @EndIndex
--计算总行数
select COUNT(a.UserId) from UserInfo as a 
where a.UserStatus&64=64 and a.ParentId=@ParentId"
                .FormatWith(orderby)
            , new SqlParameter("@ParentId", SqlDbType.VarChar, 20) { Value = parentId }
            , new SqlParameter("@StartIndex", SqlDbType.Int) { Value = startIndex }
            , new SqlParameter("@EndIndex", SqlDbType.Int) { Value = endIndex }
            );
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetAuditingListByPage(List<string> fields, List<object> values, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by a." + orderby);
            }
            else
            {
                strSql.Append("order by a.CreateTime desc");
            }
            strSql.Append(")AS Row, a.*,b.DisplayName as RoleName from UserInfo as a left join RoleInfo as b on a.RoleId=b.RoleID  where UserStatus&64=64 ");
            List<SqlParameter> parm = new List<SqlParameter>(); if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    if (fields[i].ToLower().StartsWith("like_"))
                    {
                        strSql.Append(" AND CHARINDEX(@" + fields[i] + ",a." + fields[i].Split('_')[1] + ")>0 ");
                    }
                    else if (fields[i].ToLower().StartsWith("start_"))
                    {
                        strSql.Append(" AND DATEDIFF(day,@" + fields[i] + ",a." + fields[i].Split('_')[1] + ")>=0");
                    }
                    else if (fields[i].ToLower().StartsWith("end_"))
                    {
                        strSql.Append(" AND DATEDIFF(day,@" + fields[i] + ",a." + fields[i].Split('_')[1] + ")<=0");
                    }
                    else if (fields[i].ToLower().StartsWith("and_"))
                    {
                        strSql.Append(" AND (a." + fields[i].Split('_')[1] + "& @" + fields[i] + ")=@" + fields[i] + " ");
                    }
                    else
                    {
                        strSql.Append(" AND a." + fields[i] + "=@" + fields[i] + " ");
                    }
                    parm.Add(new SqlParameter("@" + fields[i], values[i]));
                }
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            strSql.Append("; " + CreateCountSql(fields, values));
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }
        /// <summary>
        /// 获取指定父级用户及状态值的列表
        /// </summary>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public DataTable GetList(string parentId, int status)
        {
            return DbHelperSQL.GetDataTable("select a.*,b.DisplayName as RoleName from UserInfo as a left join RoleInfo as b on a.RoleId=b.RoleID where ParentId=@ParentId and UserStatus&@UserStatus=@UserStatus order by a.LastUpdateTime"
                , new SqlParameter("@ParentId", parentId)
                , new SqlParameter("@UserStatus", status));
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(List<string> fields, List<object> values)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM UserInfo where 1=1 ");
            List<SqlParameter> parm = new List<SqlParameter>(); if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    if (fields[i].ToLower().StartsWith("like_"))
                    {
                        strSql.Append(" AND CHARINDEX(@" + fields[i] + "," + fields[i].Split('_')[1] + ")>0 ");
                    }
                    else if (fields[i].ToLower().StartsWith("start_"))
                    {
                        strSql.Append(" AND DATEDIFF(day,@" + fields[i] + "," + fields[i].Split('_')[1] + ")>=0");
                    }
                    else if (fields[i].ToLower().StartsWith("end_"))
                    {
                        strSql.Append(" AND DATEDIFF(day,@" + fields[i] + "," + fields[i].Split('_')[1] + ")<=0");
                    }
                    else if (fields[i].ToLower().StartsWith("and_"))
                    {
                        strSql.Append(" AND (" + fields[i].Split('_')[1] + "& @" + fields[i] + ")=@" + fields[i] + " ");
                    }
                    else if (fields[i].ToLower().StartsWith("not_"))
                    {
                        strSql.Append("and " + fields[i].Split('_')[1] + " !=@" + fields[i]);
                    }
                    else
                    {
                        strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    }
                    parm.Add(new SqlParameter("@" + fields[i], values[i]));
                }
            }
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
            DataTable dt = ds.Tables[0];
            int a = dt.Rows.Count;
            //return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
            return ds;
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetListUnion(List<string> fields, List<object> values)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@" select * FROM (
                            select row_number() over(order by u.UserId) as Row, u.*,c.CafesId,c.CafesName,c.Province as CProvince,c.City as CCity
                            ,c.Area as CArea,c.Address as CafeAdress,c.Description,c.CreateTime as CafeCreateTime from UserInfo u
                            left join CafesInfos c on u.UserId=c.UserId
                            where u.RoleId in (1,2,3,4)
                            ) as t where 1=1 ");
            List<SqlParameter> parm = new List<SqlParameter>();
            //定时器
            List<string> list = new List<string>();
            int num = 0;
            if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    //判断第几次用此参数
                    string sign = fields[i].Substring(fields[i].IndexOf('_') + 1);
                    num = GetCount(list, sign);
                    if (fields[i].StartsWith("like_"))
                    {
                        strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                    }
                    else if (fields[i].StartsWith("not_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                        }
                        else
                        {
                            strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + num + "  ");
                        }
                    }
                    else if (fields[i].StartsWith("lt_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                        }
                        else
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + num + "  ");
                        }
                    }
                    else if (fields[i].StartsWith("gt_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                        }
                        else
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + num + "  ");
                        }
                    }
                    else if (fields[i].StartsWith("&_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append(" AND  " + fields[i].Substring(2) + "&@" + fields[i].Substring(2) + "=@" + fields[i].Substring(2) + "   ");
                        }
                        else
                        {
                            strSql.Append(" AND  " + fields[i].Substring(2) + "&@" + fields[i].Substring(2) + num + "=@" + fields[i].Substring(2) + num + "   ");
                        }
                    }
                    else if (fields[i].StartsWith("&not_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append(" AND  " + fields[i].Substring(5) + "&@" + fields[i].Substring(5) + "!=@" + fields[i].Substring(5) + "   ");
                        }
                        else
                        {
                            strSql.Append(" AND  " + fields[i].Substring(5) + "&@" + fields[i].Substring(5) + num + "!=@" + fields[i].Substring(5) + num + "   ");
                        }
                    }
                    else
                    {
                        strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    }
                    //判断第几次用此参数
                    if (num == 0)
                    {
                        parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                    }
                    else
                    {
                        parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1) + num, values[i]));
                    }
                    list.Add(fields[i].Substring(fields[i].IndexOf('_') + 1));
                }
            }
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }
        /// <summary>
        /// (重载加入RoleId)获得数据列表
        /// </summary>
        public DataSet GetListUnion(List<string> fields, List<object> values, string roleId)
        {
            StringBuilder strSql = new StringBuilder();
            List<SqlParameter> parm = new List<SqlParameter>();
            strSql.Append("select * ");
            strSql.Append(@" FROM (
                            select row_number() over(order by u.UserId) as RowId, u.*,c.CafesId,c.CafesName,c.Province as CProvince,c.City as CCity
                            ,c.Area as CArea,c.Address as CafeAdress,c.Description,c.CreateTime as CafeCreateTime from UserInfo u
                            left join CafesInfos c on u.UserId=c.UserId  ");
            //roleId的处理
            if (!string.IsNullOrEmpty(roleId))
            {
                strSql.Append(@" where u.RoleId in (" + roleId + @")");
                parm.Add(new SqlParameter("@RoleId", roleId));
            }
            strSql.Append(@" ) as t where 1=1 ");
            //定时器
            List<string> list = new List<string>();
            int num = 0;
            if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    //判断第几次用此参数
                    string sign = fields[i].Substring(fields[i].IndexOf('_') + 1);
                    num = GetCount(list, sign);
                    if (fields[i].StartsWith("like_"))
                    {
                        strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                    }
                    else if (fields[i].StartsWith("not_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                        }
                        else
                        {
                            strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + num + "  ");
                        }
                    }
                    else if (fields[i].StartsWith("lt_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                        }
                        else
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + num + "  ");
                        }
                    }
                    else if (fields[i].StartsWith("gt_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                        }
                        else
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + num + "  ");
                        }
                    }
                    else if (fields[i].StartsWith("&_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append(" AND  " + fields[i].Substring(2) + "&@" + fields[i].Substring(2) + "=@" + fields[i].Substring(2) + "   ");
                        }
                        else
                        {
                            strSql.Append(" AND  " + fields[i].Substring(2) + "&@" + fields[i].Substring(2) + num + "=@" + fields[i].Substring(2) + num + "   ");
                        }
                    }
                    else if (fields[i].StartsWith("&not_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append(" AND  " + fields[i].Substring(5) + "&@" + fields[i].Substring(5) + "!=@" + fields[i].Substring(5) + "   ");
                        }
                        else
                        {
                            strSql.Append(" AND  " + fields[i].Substring(5) + "&@" + fields[i].Substring(5) + num + "!=@" + fields[i].Substring(5) + num + "   ");
                        }
                    }
                    else
                    {
                        strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    }
                    //判断第几次用此参数
                    if (num == 0)
                    {
                        parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                    }
                    else
                    {
                        parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1) + num, values[i]));
                    }
                    list.Add(fields[i].Substring(fields[i].IndexOf('_') + 1));
                }
            }
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetListUnionCus(List<string> fields, List<object> values)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(@" FROM (
                            select row_number() over(order by u.UserId) as RowId, u.*,c.CafesId,c.CafesName,c.Province as CProvince,c.City as CCity
                            ,c.Area as CArea,c.Address as CafeAdress,c.Description,c.CreateTime as CafeCreateTime from UserInfo u
                            left join CafesInfos c on u.UserId=c.UserId
                            where u.RoleId=5
                            ) as t where 1=1 ");
            List<SqlParameter> parm = new List<SqlParameter>(); if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    parm.Add(new SqlParameter("@" + fields[i], values[i]));
                }
            }
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }
        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, List<string> fields, List<object> values, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" FROM UserInfo where 1=1 ");
            List<SqlParameter> parm = new List<SqlParameter>(); if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    parm.Add(new SqlParameter("@" + fields[i], values[i]));
                }
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }
        public DataSet GetListThrowItemList(List<string> fields, List<object> values)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSqlP = new StringBuilder();
            List<SqlParameter> parm = new List<SqlParameter>();
            strSqlP.Append(@" select u.*,r.DisplayName from UserInfo  u
                             left join RoleInfo r on u.RoleId=r.RoleID
                             where u.RoleId in 
                             (
                                  select ItemValue from (
                                  select lic.*,il.ItemText as PItemText from ItemList il 
                                  left join ItemDic id on il.ItemID=id.ItemParentId 
                                  left join ItemList lic on id.ItemId=lic.ItemID
                                  ) t
                                  where 1=1  ");
            strSql.Append(@" )  ");
            //定时器
            List<string> list = new List<string>();
            int num = 0;
            //处理结束
            if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    //判断第几次用此参数
                    string sign = fields[i].Substring(fields[i].IndexOf('_') + 1);
                    num = GetCount(list, sign);
                    if (fields[i].StartsWith("parent_"))
                    {
                        #region Parent条件加入
                        fields[i] = fields[i].Substring(7);
                        if (fields[i].StartsWith("like_"))
                        {
                            strSqlP.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                        }
                        else if (fields[i].StartsWith("not_"))
                        {
                            if (num == 0)
                            {
                                strSqlP.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                            }
                            else
                            {
                                strSqlP.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + num + "  ");
                            }
                        }
                        else if (fields[i].StartsWith("lt_"))
                        {
                            if (num == 0)
                            {
                                strSqlP.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                            }
                            else
                            {
                                strSqlP.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + num + "  ");
                            }
                        }
                        else if (fields[i].StartsWith("gt_"))
                        {
                            if (num == 0)
                            {
                                strSqlP.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                            }
                            else
                            {
                                strSqlP.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + num + "  ");
                            }
                        }
                        else if (fields[i].StartsWith("&_"))
                        {
                            if (num == 0)
                            {
                                strSqlP.Append(" AND  " + fields[i].Substring(2) + "&@" + fields[i].Substring(2) + "=@" + fields[i].Substring(2) + "   ");
                            }
                            else
                            {
                                strSqlP.Append(" AND  " + fields[i].Substring(2) + "&@" + fields[i].Substring(2) + num + "=@" + fields[i].Substring(2) + num + "   ");
                            }
                        }
                        else if (fields[i].StartsWith("&not_"))
                        {
                            if (num == 0)
                            {
                                strSqlP.Append(" AND  " + fields[i].Substring(5) + "&@" + fields[i].Substring(5) + "!=@" + fields[i].Substring(5) + "   ");
                            }
                            else
                            {
                                strSqlP.Append(" AND  " + fields[i].Substring(5) + "&@" + fields[i].Substring(5) + num + "!=@" + fields[i].Substring(5) + num + "   ");
                            }
                        }
                        else
                        {
                            strSqlP.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                        }
                        #endregion
                    }
                    else
                    {
                        #region 正常参数加载
                        if (fields[i].StartsWith("like_"))
                        {
                            strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                        }
                        else if (fields[i].StartsWith("not_"))
                        {
                            if (num == 0)
                            {
                                strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                            }
                            else
                            {
                                strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + num + "  ");
                            }
                        }
                        else if (fields[i].StartsWith("lt_"))
                        {
                            if (num == 0)
                            {
                                strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                            }
                            else
                            {
                                strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + num + "  ");
                            }
                        }
                        else if (fields[i].StartsWith("gt_"))
                        {
                            if (num == 0)
                            {
                                strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                            }
                            else
                            {
                                strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + num + "  ");
                            }
                        }
                        else if (fields[i].StartsWith("&_"))
                        {
                            if (num == 0)
                            {
                                strSql.Append(" AND  " + fields[i].Substring(2) + "&@" + fields[i].Substring(2) + "=@" + fields[i].Substring(2) + "   ");
                            }
                            else
                            {
                                strSql.Append(" AND  " + fields[i].Substring(2) + "&@" + fields[i].Substring(2) + num + "=@" + fields[i].Substring(2) + num + "   ");
                            }
                        }
                        else if (fields[i].StartsWith("&not_"))
                        {
                            if (num == 0)
                            {
                                strSql.Append(" AND  " + fields[i].Substring(5) + "&@" + fields[i].Substring(5) + "!=@" + fields[i].Substring(5) + "   ");
                            }
                            else
                            {
                                strSql.Append(" AND  " + fields[i].Substring(5) + "&@" + fields[i].Substring(5) + num + "!=@" + fields[i].Substring(5) + num + "   ");
                            }
                        }
                        else
                        {
                            strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                        }
                        #endregion
                    }
                    //判断第几次用此参数
                    if (num == 0)
                    {
                        parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                    }
                    else
                    {
                        parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1) + num, values[i]));
                    }
                    list.Add(fields[i].Substring(fields[i].IndexOf('_') + 1));
                }
            }
            strSqlP.Append(strSql);
            return DbHelperSQL.Query(strSqlP.ToString(), parm.ToArray());
        }
        /// <summary>
        /// 查询记录总行数
        /// </summary>
        /// <param name="fields">查询字段，如果有判断运算符，用,逗号隔开</param>
        /// <param name="values">查询字段值，当fields中含有in运算符时,会自动进行参数化处理</param>
        /// <returns></returns>
        public int GetRecordCount(List<string> fields, List<object> values)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM UserInfo where 1=1 ");
            List<SqlParameter> parm = new List<SqlParameter>();
            if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    string[] fieldOptions = fields[i].Split(',');
                    string fieldName = fieldOptions[0];
                    if (fieldOptions.Length > 1)
                    {
                        if (fieldOptions[1] == "in")
                        {
                            List<int> curValues = values[i] as List<int>;
                            if (curValues == null)
                                throw new Exception("传递参数错误");
                            StringBuilder p1 = new StringBuilder();
                            string fvN = string.Empty;
                            for (int n = 0; n < curValues.Count; n++)
                            {
                                fvN = string.Format("@{0}{1}", fieldName, n.ToString());
                                p1.Append(fvN + ",");
                                parm.Add(new SqlParameter(fvN, curValues[n]));
                            }
                            strSql.AppendFormat(" and {0} in ({1})", fieldName, p1.ToString().TrimEnd(','));
                        }
                        else
                        {
                            strSql.AppendFormat(" AND {0}{1}@{2}", fieldName, fieldOptions[1], fieldName);
                            parm.Add(new SqlParameter("@" + fieldName, values[i]));
                        }
                    }
                    else
                    {
                        strSql.Append(" AND " + fieldName + "=@" + fields[i] + " ");
                        parm.Add(new SqlParameter("@" + fieldName, values[i]));
                    }
                }
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parm.ToArray());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        /// <param name="fields">查询字段，如果有判断运算符，【用,逗号隔开】</param>
        /// <param name="values">查询字段值，当fields中含有in运算符时,会自动进行参数化处理</param>
        /// <param name="orderby">排序字段，默认为UserId desc</param>
        /// <param name="pageIndex">查询起始行</param>
        /// <param name="endIndex">查询结束行</param>
        /// <returns></returns>
        public DataSet GetListByPage(List<string> fields, List<object> values, string orderby, int pageIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT TT.*,b.DisplayName as RoleName,dbo.f_getUserSecuritySet(userid) as SecuritySet FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.UserId desc");
            }
            strSql.Append(")AS Row, T.*  from UserInfo T where 1=1");//UserId!='Root'
            List<SqlParameter> parm = new List<SqlParameter>();
            if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    string[] fieldOptions = fields[i].Split(',');
                    string fieldName = fieldOptions[0];
                    if (fieldOptions.Length > 1)
                    {
                        if (fieldOptions[1] == "in")
                        {
                            List<int> curValues = values[i] as List<int>;
                            if (curValues == null)
                                throw new Exception("传递参数错误");
                            StringBuilder p1 = new StringBuilder();
                            string fvN = string.Empty;
                            for (int n = 0; n < curValues.Count; n++)
                            {
                                fvN = string.Format("@{0}{1}", fieldName, n.ToString());
                                p1.Append(fvN + ",");
                                parm.Add(new SqlParameter(fvN, curValues[n]));
                            }
                            strSql.AppendFormat(" and {0} in ({1})", fieldName, p1.ToString().TrimEnd(','));
                        }
                        else
                        {
                            strSql.AppendFormat(" AND {0}{1}@{2}", fieldName, fieldOptions[1], fieldName);
                            parm.Add(new SqlParameter("@" + fieldName, values[i]));
                        }
                    }
                    else
                    {
                        strSql.Append(" AND " + fieldName + "=@" + fields[i] + " ");
                        parm.Add(new SqlParameter("@" + fieldName, values[i]));
                    }
                }
            }
            strSql.Append(" ) as TT left join  RoleInfo as b on TT.RoleId=b.RoleID");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", pageIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }
        /// <summary>
        /// 返回输入的两个用户名是否为亲属关系
        /// </summary>
        /// <param name="parentId">父级用户</param>
        /// <param name="childId">子级用户</param>
        /// <returns></returns>
        public bool IsFamily(string parentId, string childId)
        {
            if (string.IsNullOrEmpty(parentId) || string.IsNullOrEmpty(childId))
                return false;
            SqlParameter[] parm = 
            {
                new SqlParameter("@ParentId",SqlDbType.NChar,50),
                new SqlParameter("@ChildId",SqlDbType.NChar,50)
            };
            parm[0].Value = parentId;
            parm[1].Value = childId;
            int result = DbHelperSQL.GetIntByPro("[UserInfo_QueryRelation]", parm);
            return result == 1;
        }
        /// <summary>
        /// 返回输入的两个用户名是否为亲属关系
        /// </summary>
        /// <param name="parentId">父级用户</param>
        /// <param name="childId">子级用户</param>
        /// <returns></returns>
        public bool IsLeader(string leaderId, string followerId)
        {
            if (string.IsNullOrEmpty(leaderId) || string.IsNullOrEmpty(followerId))
                return false;
            SqlParameter[] parm = 
            {
                new SqlParameter("@leaderId",SqlDbType.NChar,50),
                new SqlParameter("@followerId",SqlDbType.NChar,50)
            };
            parm[0].Value = leaderId;
            parm[1].Value = followerId;
            return DbHelperSQL.Exists("select COUNT(1) from UserRelation where ParentId=@leaderId and UserId=@followerId", parm);
        }
        /// <summary>
        /// 给对网吧用户的信用额度进行调整的相关处理，更新目标用户可用余额,信用额度；插入额度调整记录
        /// </summary>
        /// <param name="targetId">目标用户</param>
        /// <param name="operatorId">操作者</param>
        /// <param name="oldCredit">旧额度</param>
        /// <param name="newCredit">新额度</param>
        /// <param name="remark">操作备忘</param>
        /// <returns></returns>
        public bool AdjustCredit(string targetId, string operatorId, decimal oldCredit, decimal newCredit, string remark)
        {
            if (string.IsNullOrEmpty(targetId) || string.IsNullOrEmpty(operatorId))
                return false;
            SqlParameter[] parm = 
            {                
                new SqlParameter("@TargetUser",SqlDbType.VarChar,20),
                new SqlParameter("@Operator",SqlDbType.VarChar,20)
                ,new SqlParameter("@OldCredit",SqlDbType.Money)
                ,new SqlParameter("@NewCredit",SqlDbType.Money)
                ,new SqlParameter("@Remark",SqlDbType.NVarChar,20),
            };
            parm[0].Value = targetId;
            parm[1].Value = operatorId;
            parm[2].Value = oldCredit;
            parm[3].Value = newCredit;
            parm[4].Value = remark;
            int result = DbHelperSQL.GetIntByPro("[AdjustCredit]", parm);
            return result == 1;
        }
        /// <summary>
        /// 给用户充值
        /// </summary>
        /// <param name="targetId">目标用户</param>
        /// <param name="operatorId">操作用户</param>
        /// <param name="tradeMoney">涉及金额</param>
        /// <returns></returns>
        public bool RechargeMoneyToCafe(string targetId, string operatorId, decimal tradeMoney,string payUserId)
        {
            if (string.IsNullOrEmpty(targetId) || string.IsNullOrEmpty(operatorId)||string.IsNullOrEmpty(payUserId))
                return false;
            SqlParameter[] parm = 
            {                
                new SqlParameter("@TargetUser",SqlDbType.VarChar,20),
                new SqlParameter("@Operator",SqlDbType.VarChar,20),
                new SqlParameter("@TradeMoney",SqlDbType.Money),
                new SqlParameter("@payUserId",SqlDbType.VarChar,20)
            };
            parm[0].Value = targetId;
            parm[1].Value = operatorId;
            parm[2].Value = tradeMoney;
            parm[3].Value = payUserId;
            int result = DbHelperSQL.GetIntByPro("[RechargeMoneyToCafe]", parm);
            return result == 1;
        }
        /// <summary>
        /// 给用户充值
        /// </summary>
        /// <param name="targetId">目标用户</param>
        /// <param name="operatorId">操作用户</param>
        /// <param name="tradeMoney">涉及金额</param>
        /// <returns></returns>
        public bool RechargeMoney(string targetId, string operatorId, decimal tradeMoney, string curUserId)
        {
            if (string.IsNullOrEmpty(targetId) || string.IsNullOrEmpty(operatorId))
                return false;
            SqlParameter[] parm = 
            {                
                new SqlParameter("@TargetUser",SqlDbType.VarChar,20),
                new SqlParameter("@Operator",SqlDbType.VarChar,20),
                new SqlParameter("@TradeMoney",SqlDbType.Money),
                new SqlParameter("@CurUserId",SqlDbType.VarChar,20)
            };
            parm[0].Value = targetId;
            parm[1].Value = operatorId;
            parm[2].Value = tradeMoney;
            parm[3].Value = curUserId;
            int result = DbHelperSQL.GetIntByPro("[RechargeMoney]", parm);
            return result == 1;
        }
        /// <summary>
        /// 用户在线支付
        /// </summary>
        /// <param name="targetId">目标用户</param>
        /// <param name="operatorId">操作用户</param>
        /// <param name="tradeMoney">涉及金额</param>
        /// <param name="gameId">游戏编号</param>
        /// <param name="gameArea">游戏区服编号</param>
        /// <param name="orderNo">订单号</param>
        /// <param name="bank">支付银行</param>
        /// <returns></returns>
        public bool RechargeMoney_Game_OnLine(string targetId, string operatorId, decimal tradeMoney, int gameId, int gameArea, string orderNo, string bank, int payInterfaceId)
        {
            if (string.IsNullOrEmpty(targetId) || string.IsNullOrEmpty(operatorId))
                return false;
            SqlParameter[] parm = 
            {                
                new SqlParameter("@TargetUser",SqlDbType.VarChar,20)
                ,new SqlParameter("@Operator",SqlDbType.VarChar,20)
                ,new SqlParameter("@TradeMoney",SqlDbType.Money)
                ,new SqlParameter("@GameId",SqlDbType.Int)
                ,new SqlParameter("@GameArea",SqlDbType.Int)
                ,new SqlParameter("@OrderNo",SqlDbType.VarChar,20)
                ,new SqlParameter("@Bank",SqlDbType.VarChar,20)
                ,new SqlParameter("@PayInterfaceId",SqlDbType.Int)
            };
            parm[0].Value = targetId;
            parm[1].Value = operatorId;
            parm[2].Value = tradeMoney;
            parm[3].Value = gameId;
            parm[4].Value = gameArea;
            parm[5].Value = orderNo;
            parm[6].Value = bank;
            parm[7].Value = payInterfaceId;
            int result = DbHelperSQL.GetIntByPro("[RechargeMoney_Game_OnLine]", parm);
            return result == 1;
        }
        /// <summary>
        /// 用户在线充值到平台
        /// </summary>
        /// <param name="targetId">目标用户</param>
        /// <param name="operatorId">操作用户</param>
        /// <param name="tradeMoney">涉及金额</param>
        /// <param name="orderNo">订单号</param>
        /// <param name="bank">支付银行</param>
        /// <returns></returns>
        public bool RechargeMoney_Plat_OL(string targetId, string operatorId, decimal tradeMoney, string orderNo, string bank, int payInterfaceId)
        {
            if (string.IsNullOrEmpty(targetId) || string.IsNullOrEmpty(operatorId))
                return false;
            SqlParameter[] parm = 
            {                
                new SqlParameter("@TargetUser",SqlDbType.VarChar,20)
                ,new SqlParameter("@Operator",SqlDbType.VarChar,20)
                ,new SqlParameter("@TradeMoney",SqlDbType.Money)
                ,new SqlParameter("@OrderNo",SqlDbType.VarChar,20)
                ,new SqlParameter("@Bank",SqlDbType.VarChar,20)
                ,new SqlParameter("@PayInterfaceId",SqlDbType.Int)
            };
            parm[0].Value = targetId;
            parm[1].Value = operatorId;
            parm[2].Value = tradeMoney;
            parm[3].Value = orderNo;
            parm[4].Value = bank;
            parm[5].Value = payInterfaceId;
            int result = DbHelperSQL.GetIntByPro("[RechargeMoney_Plat_OL]", parm);
            return result == 1;
        }
        /// <summary>
        /// 用户在线充值到平台
        /// </summary>
        /// <param name="targetId">目标用户</param>
        /// <param name="operatorId">操作用户</param>
        /// <param name="tradeMoney">涉及金额</param>
        /// <param name="orderNo">订单号</param>
        /// <param name="bank">支付银行</param>
        /// <returns></returns>
        public bool RechargeMoney_Plat_OL_Cafe(string targetId, string operatorId, decimal tradeMoney, string orderNo, string bank, int payInterfaceId)
        {
            if (string.IsNullOrEmpty(targetId) || string.IsNullOrEmpty(operatorId))
                return false;
            SqlParameter[] parm = 
            {                
                new SqlParameter("@TargetUser",SqlDbType.VarChar,20)
                ,new SqlParameter("@Operator",SqlDbType.VarChar,20)
                ,new SqlParameter("@TradeMoney",SqlDbType.Money)
                ,new SqlParameter("@OrderNo",SqlDbType.VarChar,20)
                ,new SqlParameter("@Bank",SqlDbType.VarChar,20)
                ,new SqlParameter("@PayInterfaceId",SqlDbType.Int)
            };
            parm[0].Value = targetId;
            parm[1].Value = operatorId;
            parm[2].Value = tradeMoney;
            parm[3].Value = orderNo;
            parm[4].Value = bank;
            parm[5].Value = payInterfaceId;
            int result = DbHelperSQL.GetIntByPro("[RechargeMoney_Plat_OL_Cafe]", parm);
            return result == 1;
        }
        /// <summary>
        /// 给用户充值
        /// </summary>
        /// <param name="targetId">目标用户</param>
        /// <param name="operatorId">操作用户</param>
        /// <param name="tradeMoney">涉及金额</param>
        /// <param name="gameId">游戏编号</param>
        /// <param name="gameArea">游戏区服编号</param>
        /// <param name="payUserId">使用的支付账户</param>        
        /// <returns></returns>
        public bool RechargeMoney_Game(string targetId, string operatorId, decimal tradeMoney, int gameId, int gameArea, string curUserId, string payUserId)
        {
            if (string.IsNullOrEmpty(targetId) || string.IsNullOrEmpty(operatorId))
                return false;
            SqlParameter[] parm = 
            {                
                new SqlParameter("@TargetUser",SqlDbType.VarChar,20)
                ,new SqlParameter("@Operator",SqlDbType.VarChar,20)
                ,new SqlParameter("@TradeMoney",SqlDbType.Money)
                ,new SqlParameter("@GameId",SqlDbType.Int)
                ,new SqlParameter("@GameArea",SqlDbType.Int)
                ,new SqlParameter("@CurUserId",SqlDbType.VarChar,20)
                ,new SqlParameter("@PayUserId",SqlDbType.VarChar,20){Value=payUserId}
            };
            parm[0].Value = targetId;
            parm[1].Value = operatorId;
            parm[2].Value = tradeMoney;
            parm[3].Value = gameId;
            parm[4].Value = gameArea;
            parm[5].Value = curUserId;
            int result = DbHelperSQL.GetIntByPro("[RechargeMoney_Game]", parm);
            return result == 1;
        }
        /// <summary>
        /// 获取父级用户名
        /// </summary>
        /// <param name="userId">需要查询的用户名</param>
        /// <returns></returns>
        public string GetParentId(string userId)
        {
            if (string.IsNullOrEmpty(userId) || string.IsNullOrEmpty(userId))
                return null;
            SqlParameter[] parm = 
            {
                new SqlParameter("UserId",SqlDbType.NChar,50)
            };
            parm[0].Value = userId;
            object result = DbHelperSQL.GetSingle("select ParentId from UserInfo where UserId=@UserId", parm);
            if ((Object.Equals(result, null)) || (Object.Equals(result, System.DBNull.Value)))
            {
                return null;
            }
            return result.ToString();
        }
        UserInfo IUserInfoDAL.GetAgent(int province, int city, int area)
        {
            return GetAgent(province, city, area);
        }
        /// <summary>
        /// 获取用户角色
        /// </summary>
        /// <param name="userId">需要查询的用户名</param>
        /// <returns></returns>
        public int GetUserRoleId(string userId)
        {
            if (string.IsNullOrEmpty(userId) || string.IsNullOrEmpty(userId))
                return -1;
            SqlParameter[] parm = 
            {
                new SqlParameter("UserId",SqlDbType.VarChar,50)
            };
            parm[0].Value = userId;
            object result = DbHelperSQL.GetSingle("select RoleId from UserInfo where UserId=@UserId", parm);
            if ((Object.Equals(result, null)) || (Object.Equals(result, System.DBNull.Value)))
            {
                return -1;
            }
            return ConvertUtil.ObjectToInt(result);
        }
        /// <summary>
        /// 判断是否是直营店下面的推广员
        /// </summary>
        /// <param name="userId">需要查询的用户名</param>
        /// <returns></returns>
        public bool IsZhiyingdianTgy(string userId)
        {
            if (string.IsNullOrEmpty(userId) || string.IsNullOrEmpty(userId))
                return false;
            SqlParameter[] parm = 
            {
                new SqlParameter("UserId",SqlDbType.VarChar,50)
            };
            parm[0].Value = userId;
            StringBuilder sb = new StringBuilder();
            sb.Append("select * from UserInfo where UserId in( ");
            sb.Append("select ParentId from UserInfo where  UserId=@UserId and RoleId=" + (int)RoleType.推广员 + ")");
            sb.Append("and RoleId=" + (int)RoleType.直营店);
            object result = DbHelperSQL.GetSingle(sb.ToString(), parm);
            if ((Object.Equals(result, null)) || (Object.Equals(result, System.DBNull.Value)))
            {
                return false;
            }
            return true;
        }
        /// <summary>
        /// 获取父级角色ID
        /// </summary>
        /// <param name="userId">需要查询的用户名</param>
        /// <returns></returns>
        public int GetParentRoleId(string userId)
        {
            if (string.IsNullOrEmpty(userId) || string.IsNullOrEmpty(userId))
                return -1;
            SqlParameter[] parm = 
            {
                new SqlParameter("UserId",SqlDbType.VarChar,50)
            };
            parm[0].Value = userId;
            object result = DbHelperSQL.GetSingle("select RoleId from UserInfo where UserId in(select ParentID from UserInfo where UserId=@UserId)", parm);
            if ((Object.Equals(result, null)) || (Object.Equals(result, System.DBNull.Value)))
            {
                return -1;
            }
            return ConvertUtil.ObjectToInt(result);
        }
        /// <summary>
        /// 父子层级水平
        /// </summary>
        /// <param name="ParentId"></param>
        /// <param name="SonId"></param>
        /// <param name="level">0表示同级，-1没级别</param>
        /// <returns></returns>
        public void GetParentandSonLevel(string ParentId, string SonId, out int level)
        {
            SqlParameter[] parameters =
            {
               new SqlParameter("ParentId",SqlDbType.VarChar,20),
               new SqlParameter("SonId",SqlDbType.VarChar,20),
               new SqlParameter("Level",SqlDbType.Int)
            };
            parameters[0].Value = ParentId;
            parameters[1].Value = SonId;
            parameters[2].Direction = ParameterDirection.Output;
            DbHelperSQL.ProcedureQury("QueryParentandSonLevel", parameters);
            level = Convert.ToInt32(parameters[2].Value);
        }
        /// <summary>
        /// 返回上级用户数据列表
        /// </summary>
        /// <param name="UserId">用户ID</param>
        /// <returns></returns>
        public DataTable GetParentList(string UserId)
        {
            SqlParameter[] parm = 
            {
                new SqlParameter("UserId",SqlDbType.NChar,50)
            };
            parm[0].Value = UserId;
            return DbHelperSQL.GetDataTable("select * from UserInfo where UserId in(select ParentID from UserInfo where UserId=@UserId)", parm);
        }
        /// <summary>
        /// 通过父级用户返回下一下所属的用户
        /// </summary>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public List<string> GetUserList(string parentId)
        {
            List<string> userList = new List<string>();
            if (string.IsNullOrEmpty(parentId))
                return userList;
            DataTable dtb = DbHelperSQL.GetDataTable("select top 20 UserId from UserInfo where ParentId=@ParentId", new SqlParameter("@ParentId", parentId));
            if (dtb != null && dtb.Rows.Count > 0)
            {
                foreach (DataRow dr in dtb.Rows)
                {
                    userList.Add(dr["UserId"].ToString());
                }
            }
            return userList;
        }
        /// <summary>
        /// 通过父级用户返回下一下所属的用户
        /// </summary>
        /// <param name="parentId">查询的父级账号</param>
        /// <param name="isAdmin">如果是系统管理员，则不显示玩家账号</param>
        /// <param name="orderby">默认采用CreateTime desc排序</param>
        /// <param name="isFilterShadow">是否过滤分身账号</param>
        /// <returns></returns>
        public List<List<dynamic>> GetUserList2(string parentId, bool isAdmin, string orderby, bool isFilterShadow)
        {
            List<List<dynamic>> userList = new List<List<dynamic>>();
            if (string.IsNullOrEmpty(parentId))
                return userList;
            if (string.IsNullOrEmpty(orderby))
                orderby = "CreateTime desc";
            DataTable dtb = DbHelperSQL.GetDataTable(
                string.Format("select UserId,RoleId from UserInfo where ParentId=@ParentId{0}{2} order by {1}", (isAdmin ? " and RoleId!=6" : ""), orderby, (isFilterShadow ? " and RoleId not in (7,8,9,10,11,12,13,14,15,17,18,19)" : ""))
                , new SqlParameter("@ParentId", parentId));
            if (dtb != null && dtb.Rows.Count > 0)
            {
                foreach (DataRow dr in dtb.Rows)
                {
                    List<dynamic> ls = new List<dynamic>();
                    ls.Add(dr["UserId"]);
                    ls.Add(dr["RoleId"]);
                    userList.Add(ls);
                }
            }
            return userList;
        }
        /// <summary>
        /// 通过查询限制，返回用户名和姓名的列表
        /// </summary>        
        /// <param name="quserId">查询的用户名的前一部分</param>
        /// <param name="parentId">查询用户的父级用户名</param>
        /// <param name="limitCount">返回记录的条数</param>
        /// <param name="orderby">默认采用CreateTime desc排序</param>
        /// <returns></returns>
        public List<string[]> GetUserList3(string quserId, string parentId, int limitCount, string orderby)
        {
            List<string[]> userList = new List<string[]>();
            if (string.IsNullOrEmpty(quserId) || string.IsNullOrEmpty(parentId))
                return userList;
            if (string.IsNullOrEmpty(orderby))
                orderby = "CreateTime desc";
            DataTable dtb = DbHelperSQL.GetDataTable(string.Format("select top {0} UserId,RealityName from UserInfo where ParentId=@ParentId and UserId like @UserId order by {1}", limitCount, orderby)
                , new SqlParameter("@ParentId", parentId)
                , new SqlParameter("@UserId", quserId + "%")
                );
            if (dtb != null && dtb.Rows.Count > 0)
            {
                foreach (DataRow dr in dtb.Rows)
                {
                    userList.Add(new string[] { dr["UserId"].ToString(), dr["RealityName"].ToString() });
                }
            }
            return userList;
        }
        /// <summary>
        /// 通过查询限制，返回实体网吧用户名和姓名的列表
        /// </summary>        
        /// <param name="quserId">查询的用户名的前一部分</param>
        /// <param name="parentId">查询用户的父级用户名</param>
        /// <param name="limitCount">返回记录的条数</param>
        /// <param name="orderby">默认采用CreateTime desc排序</param>
        /// <returns></returns>
        public List<string[]> GetUserList5(string quserId, string parentId, int limitCount, string orderby)
        {
            List<string[]> userList = new List<string[]>();
            if (string.IsNullOrEmpty(quserId) || string.IsNullOrEmpty(parentId))
                return userList;
            if (string.IsNullOrEmpty(orderby))
                orderby = "CreateTime desc";
            DataTable dtb = DbHelperSQL.GetDataTable(string.Format("select top {0} UserId,RealityName from UserInfo where ParentId=@ParentId and RoleId in (2,3,4) and UserId like @UserId order by {1}", limitCount, orderby)
                , new SqlParameter("@ParentId", parentId)
                , new SqlParameter("@UserId", quserId + "%")
                );
            if (dtb != null && dtb.Rows.Count > 0)
            {
                foreach (DataRow dr in dtb.Rows)
                {
                    userList.Add(new string[] { dr["UserId"].ToString(), dr["RealityName"].ToString() });
                }
            }
            return userList;
        }
        /// <summary>
        /// 通过查询限制，返回实体玩家用户名和昵称的列表
        /// </summary>        
        /// <param name="quserId">查询的用户名的前一部分</param>
        /// <param name="limitCount">返回记录的条数</param>
        /// <param name="orderby">默认采用CreateTime desc排序</param>
        /// <returns></returns>
        public List<string[]> GetUserList6(string quserId, int limitCount, string orderby)
        {
            List<string[]> userList = new List<string[]>();
            if (string.IsNullOrEmpty(quserId))
                return userList;
            if (string.IsNullOrEmpty(orderby))
                orderby = "CreateTime desc";
            DataTable dtb = DbHelperSQL.GetDataTable(string.Format("select top {0} UserId,NickName from UserInfo where RoleId=6 and UserId like @UserId order by {1}", limitCount, orderby)
                , new SqlParameter("@UserId", quserId + "%")
                );
            if (dtb != null && dtb.Rows.Count > 0)
            {
                foreach (DataRow dr in dtb.Rows)
                {
                    userList.Add(new string[] { dr["UserId"].ToString(), dr["NickName"].ToString() });
                }
            }
            return userList;
        }
        /// <summary>
        /// 返回输入用户名下一下用户的总数
        /// </summary>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public int GetFirstChildCount(string parentId)
        {
            return DbHelperSQL.GetInt("select count(ParentId) from UserInfo where ParentId=@ParentId", new SqlParameter("@ParentId", SqlDbType.VarChar, 20) { Value = parentId });
        }
        public int GetFirstChildCountPart(List<string> fields, List<object> values)
        {
            StringBuilder strSql = new StringBuilder();
            List<SqlParameter> parm = new List<SqlParameter>();
            strSql.Append(@"select count(*) from UserInfo where 1=1  ");
            //定时器
            List<string> list = new List<string>();
            int num = 0;
            if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    //判断第几次用此参数
                    string sign = fields[i].Substring(fields[i].IndexOf('_') + 1);
                    num = GetCount(list, sign);
                    if (fields[i].StartsWith("like_"))
                    {
                        strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                    }
                    else if (fields[i].StartsWith("not_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                        }
                        else
                        {
                            strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + num + "  ");
                        }
                    }
                    else if (fields[i].StartsWith("lt_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                        }
                        else
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + num + "  ");
                        }
                    }
                    else if (fields[i].StartsWith("gt_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                        }
                        else
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + num + "  ");
                        }
                    }
                    else if (fields[i].StartsWith("&_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append(" AND  " + fields[i].Substring(2) + "&@" + fields[i].Substring(2) + "=@" + fields[i].Substring(2) + "   ");
                        }
                        else
                        {
                            strSql.Append(" AND  " + fields[i].Substring(2) + "&@" + fields[i].Substring(2) + num + "=@" + fields[i].Substring(2) + num + "   ");
                        }
                    }
                    else if (fields[i].StartsWith("&not_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append(" AND  " + fields[i].Substring(5) + "&@" + fields[i].Substring(5) + "!=@" + fields[i].Substring(5) + "   ");
                        }
                        else
                        {
                            strSql.Append(" AND  " + fields[i].Substring(5) + "&@" + fields[i].Substring(5) + num + "!=@" + fields[i].Substring(5) + num + "   ");
                        }
                    }
                    else
                    {
                        strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    }
                    //判断第几次用此参数
                    if (num == 0)
                    {
                        parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                    }
                    else
                    {
                        parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1) + num, values[i]));
                    }
                    list.Add(fields[i].Substring(fields[i].IndexOf('_') + 1));
                }
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parm.ToArray());
            return Convert.ToInt32(obj);
        }
        /// <summary>
        /// 查询简单用户列表(显示少量字段)，可以选择是否包含所有上级用户
        /// </summary>
        /// <param name="containParent">是否包含所有上级用户</param>
        /// <param name="parentId">不可空 指定查询用户的父级用户</param>
        /// <param name="roleIdMax">不可空 返回的用户的角色编号的上限(不包含)，比如6，则小于玩家的用户</param>
        /// <param name="orderby">可空，默认为d.UserId 可以使用的排序字段有 d.UserId,d.RoleId,d.RealityName</param>
        /// <param name="startIndex">开始记录行号</param>
        /// <param name="endIndex">结束记录行号</param>
        /// <returns></returns>
        public DataTable GetUserListSimple(bool containParent, string parentId, int roleIdMax, string orderby, int startIndex, int endIndex)
        {
            if (string.IsNullOrEmpty(parentId))
                return null;
            return DbHelperSQL.ProcedureQury("[QueryUserListSimple]"
                , new SqlParameter[]{
                     new SqlParameter("@ContainParent",SqlDbType.Bit){Value= containParent}
                    ,new SqlParameter("@ParentId",SqlDbType.VarChar,20){Value= parentId}
                    ,new SqlParameter("@RoleIdMax",SqlDbType.Int){Value= roleIdMax}
                    ,new SqlParameter("@OrderBy",SqlDbType.VarChar,20){Value= orderby}
                    ,new SqlParameter("@StartIndex",SqlDbType.Int){Value= startIndex}
                    ,new SqlParameter("@EndIndex",SqlDbType.Int){Value= endIndex}
                });
        }
        /// <summary>
        /// 模糊查询简单用户列表(显示少量字段:UserId,RoleId,RealityName)
        /// 可以选择是否包含所有上级用户
        /// </summary>
        /// <param name="containParent">是否包含所有上级用户</param>
        /// <param name="parentId">不可空 指定查询用户的父级用户</param>
        /// <param name="tier">父级用户的层级，必须与@ParentId相匹配，玩家为0，推广员为1，实体网吧为2，连锁网吧为3，</param>
        /// <param name="userId">查询用户名的部分 不可空</param>
        /// <param name="roleIdMax">不可空 返回的用户的角色编号的上限(不包含)，比如6，则小于玩家的用户</param>
        /// <returns></returns>
        public DataTable GetUserListSimple2(bool containParent, string parentId, int tier, string userId, int roleIdMax)
        {
            if (string.IsNullOrEmpty(parentId))
                return null;
            return DbHelperSQL.ProcedureQury("[QueryUserListSimple2]"
                , new SqlParameter[]{
                     new SqlParameter("@ContainParent",SqlDbType.Bit){Value= containParent}
                    ,new SqlParameter("@ParentId",SqlDbType.VarChar,20){Value= parentId}
                    ,new SqlParameter("@Tier",SqlDbType.Int){Value= tier}
                    ,new SqlParameter("@UserId",SqlDbType.VarChar,20){Value= userId}
                    ,new SqlParameter("@RoleIdMax",SqlDbType.Int){Value= roleIdMax}
                });
        }
        /// <summary>
        /// 通过父级用户名，获取分页用户列表，分别返回用户名和姓名，并使用分页返回
        /// 只可到达推广员一级
        /// </summary>
        /// <param name="parentId"></param>
        /// <param name="orderby">排序只能使用:UserId,RealityName</param>
        /// <param name="startIndex"></param>
        /// <param name="endIndex"></param>
        /// <returns></returns>
        public List<dynamic[]> GetUserList4(string parentId, string orderby, int startIndex, int endIndex)
        {
            List<dynamic[]> userList = new List<dynamic[]>();
            if (string.IsNullOrEmpty(parentId))
                return userList;
            if (string.IsNullOrEmpty(orderby))
                orderby = "UserId desc";
            //SELECT * FROM (  SELECT ROW_NUMBER() OVER (order by t1.{0} desc)AS RowID,UserId,RealityName  from UserInfo t1 where ParentId=@ParentId) as t1_1 WHERE RowID between @StartIndex and @EndIndex
            //查询不限角色
            //select *,(select COUNT(userid) from userinfo as e1 where e1.ParentId= rt.UserId) as count from(select ROW_NUMBER() over (order by d.{0}) as RowId,d.UserId,d.RealityName,(case when c.UserId is null then 0 else 1 end) as haschild FROM UserInfo as d left join (select UserId,RealityName,RoleId from UserInfo as a where exists(select 1 from UserInfo b where b.ParentId=a.UserId)) as c on d.UserId=c.UserId where d.ParentId=@ParentId) as rt WHERE rt.RowID between @StartIndex and @EndIndex
            //限定到推广员一级为终点
            DataTable dtb = DbHelperSQL.GetDataTable(
                @"select *,
(select COUNT(userid) from userinfo as e1 
	where e1.ParentId= rt.UserId and rt.RoleId !=5 and e1.RoleId<6 )
 as count from
 (select ROW_NUMBER() over (order by d.userid) as RowId,d.UserId,d.RoleId,d.RealityName,(case when d.RoleId=5 or(d.RoleId!=5 and c.UserId is null) then 0 else 1 end) as haschild 
 FROM UserInfo as d 
 left join 
 (select UserId,RealityName,RoleId from UserInfo as a where exists(select 1 from UserInfo b where b.ParentId=a.UserId)
 ) as c on d.UserId=c.UserId where d.ParentId=@ParentId and d.RoleId<6
 ) as rt WHERE rt.RowID between @StartIndex and @EndIndex"
                , new SqlParameter("@ParentId", parentId)
                , new SqlParameter("@StartIndex", SqlDbType.Int) { Value = startIndex }
                , new SqlParameter("@EndIndex", SqlDbType.Int) { Value = endIndex }
                );
            if (dtb != null && dtb.Rows.Count > 0)
            {
                foreach (DataRow dr in dtb.Rows)
                {
                    userList.Add(new dynamic[] { dr["UserId"], dr["RealityName"], dr["haschild"], dr["count"], dr["RoleId"] });
                }
            }
            return userList;
        }
        /// <summary>
        /// 返回网吧下面相关账户
        /// </summary>
        /// <param name="parentId">父账户</param>
        /// <param name="b_chain">连锁网络账户</param>
        /// <param name="b_chaiSon">连锁网吧子账户</param>
        /// <param name="b_cafe">实体网吧账户</param>
        /// <param name="b_cafeSon">实体网站子账户</param>
        /// <param name="b_cafeExtends">推广员账户</param>
        /// <returns></returns>
        public DataTable GetAccountUserList(string parentId, bool b_chain, bool b_chaiSon, bool b_cafe, bool b_cafeSon, bool b_cafeExtends)
        {
            /*
            if (string.IsNullOrEmpty(parentId))
                return null;
            string roleList = string.Empty;
            if (b_chain)
            {
                roleList += ((int)RoleType.连锁公司).ToString() + ",";
            }
            if (b_chaiSon)
            {
                roleList += ((int)RoleType.连锁网吧公司财务员).ToString() + "," + ((int)RoleType.连锁网吧公司管理员).ToString() + ",";
            }
            if (b_cafe)
            {
                roleList += ((int)RoleType.直营店).ToString() + "," + ((int)RoleType.连锁加盟网吧).ToString() + "," + ((int)RoleType.独立网吧).ToString() + ",";
            }
            if (b_cafeSon)
            {
                roleList += ((int)RoleType.网吧管理员).ToString() + "," + ((int)RoleType.网吧收银员).ToString();
            }
            if (b_cafeExtends)
            {
                roleList += ((int)RoleType.推广员).ToString();
            }
            StringBuilder sb = new StringBuilder();
            if (roleList != string.Empty)
            {
                sb.AppendFormat("select *  from UserInfo where ParentId='{0}'", parentId);
                sb.AppendFormat(" or ParentId in(select UserId  from UserInfo where ParentId='{0}') or UserId='{0}'", parentId);
            }
            if (roleList != string.Empty)
            {
                sb.AppendFormat(" and RoleId in({0})", roleList.TrimEnd(','));
            }
            return DbHelperSQL.GetDataTable(sb.ToString());*/
            return null;
        }
        /// <summary>
        ///  返回连锁网吧下面的网吧主账户 
        /// </summary>      
        public DataTable GetCafeUserListByChain(string parentId)
        {
            if (string.IsNullOrEmpty(parentId))
                return null;
            return DbHelperSQL.GetDataTable("select UserId,NickName,RealityName from UserInfo where ParentId=@ParentId and RoleId in(" + (int)RoleType.直营店 + "," + (int)RoleType.连锁加盟网吧 + ")", new SqlParameter("@ParentId", parentId));
        }
        /// <summary>
        ///  返回实体网吧员工账户（包括自身账户)
        /// </summary>      
        public DataTable GetCafeEmployeesList(string parentId)
        {
            //if (string.IsNullOrEmpty(parentId))
            return null;
            //return DbHelperSQL.GetDataTable("select UserId,NickName,RealityName,RoleId,ParentId from UserInfo where (ParentId=@ParentId  and RoleId in(" + (int)RoleType.网吧收银员 + "," + (int)RoleType.网吧管理员 + ")) or UserId=@ParentId", new SqlParameter("@ParentId", parentId));
        }
        /// <summary>
        /// 返回实体网吧员工账户（不包括自身账户)
        /// </summary>
        public DataTable GetCafeEmployeesList2(string parentId)
        {
            //if (string.IsNullOrEmpty(parentId))
            return null;
            //return DbHelperSQL.GetDataTable("select UserId,NickName,RealityName,RoleId,ParentId from UserInfo where (ParentId=@ParentId  and RoleId in(" + (int)RoleType.网吧收银员 + "," + (int)RoleType.网吧管理员 + "))", new SqlParameter("@ParentId", parentId));
        }
        /// <summary>
        ///  返回实体网吧推广员账户（包括自身账户)
        /// </summary>      
        public DataTable GetCafeExtendList(string parentId)
        {
            if (string.IsNullOrEmpty(parentId))
                return null;
            return DbHelperSQL.GetDataTable("select UserId,NickName,RealityName,RoleId,ParentId from UserInfo where (ParentId=@ParentId  and RoleId=" + (int)RoleType.推广员 + ")  or UserId=@ParentId", new SqlParameter("@ParentId", parentId));
        }
        /// <summary>
        ///  返回实体网吧推广员账户（不包括自身账户)
        /// </summary>      
        public DataTable GetCafeExtendList2(string parentId)
        {
            if (string.IsNullOrEmpty(parentId))
                return null;
            return DbHelperSQL.GetDataTable("select UserId,NickName,RealityName,RoleId,ParentId from UserInfo where (ParentId=@ParentId  and RoleId=" + (int)RoleType.推广员 + ")", new SqlParameter("@ParentId", parentId));
        }
        /// <summary>
        /// 通过父级用户返回下一下所属的用户，并且不为子账号
        /// UserId,NickName,RealityName
        /// </summary>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public DataTable GetUserListTable(string parentId)
        {
            if (string.IsNullOrEmpty(parentId))
                return null;
            return DbHelperSQL.GetDataTable("select UserId,NickName,RealityName from UserInfo where ParentId=@ParentId and RoleId<6", new SqlParameter("@ParentId", parentId));
        }
        /// <summary>
        /// 迭代查询指定用户下属所有账户，并且使用角色列表和分页进行限定
        /// 最后返回总数,也可以当做分页总行数
        /// </summary>
        /// <param name="parentId">指定父级用户</param>
        /// <param name="roleId">限定角色编号</param>
        /// <returns></returns>
        public int GetList7RecordCount(string parentId, List<int> roleId)
        {
            if (roleId == null || roleId.Count == 0)
                return 0;
            StringBuilder sbRoleList = new StringBuilder();
            foreach (var item in roleId)
            {
                if (item < 100)
                    sbRoleList.AppendFormat("{0},", item);
            }
            if (sbRoleList.Length == 0)
                return 0;
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"declare
	@usertbl table(
	[UserId] [varchar](20) NOT NULL,
	[ParentId] [varchar](20) NULL,
	[RoleId] [int] NOT NULL,
	[tag] int not null
	)	
	INSERT INTO @usertbl ([UserId],[ParentId],[RoleId],[tag]) 
	SELECT [UserId],[ParentId],[RoleId],1 FROM UserInfo where ParentId=@UserId and RoleId in ({0})
	declare @rowCount int,@tag int,@tagnext int
	set @tag=1
	select @rowCount=COUNT(UserId) from @usertbl
	while(@rowCount>0)
	begin
		set @tagnext=@tag
		set @tag +=1
		INSERT INTO @usertbl ([UserId],[ParentId],[RoleId],[tag]) 
	SELECT [UserId],[ParentId],[RoleId],@tagnext FROM UserInfo where ParentId in (select UserId from @usertbl where tag=@tagnext) and RoleId in ({0})
		select @rowCount=COUNT(UserId) from @usertbl where tag=@tag	 
	end
	select count(UserId) from @usertbl"
                , sbRoleList.ToString().TrimEnd(','));
            return DbHelperSQL.GetInt(sb.ToString(), new SqlParameter("@UserId", parentId));
        }
        /// <summary>
        /// 迭代查询指定用户下属所有账户，并且使用角色列表和分页进行限定
        /// </summary>
        /// <param name="parentId">指定父级用户</param>
        /// <param name="roleId">限定角色编号</param>
        /// <param name="orderby">排序字段</param>
        /// <param name="pageStartIndex">分页行号开始</param>
        /// <param name="pageEndIndex">分页行号结束</param>
        /// <param name="rowCount">用于分页的总行数</param>
        /// <returns></returns>
        public DataTable GetList7(string parentId, List<int> roleId, string orderby, int pageStartIndex, int pageEndIndex, out int rowCount)
        {
            rowCount = 0;
            if (roleId == null || roleId.Count == 0)
                return null;
            StringBuilder sbRoleList = new StringBuilder();
            foreach (var item in roleId)
            {
                if (item < 100)
                    sbRoleList.AppendFormat("{0},", item);
            }
            if (sbRoleList.Length == 0)
                return null;
            if (string.IsNullOrEmpty(orderby))
                orderby = "UserId";
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"declare
@usertbl table(
[SeqNo] [int] NOT NULL,
[UserId] [varchar](20) NOT NULL,
[NickName] [nvarchar](20) NOT NULL,
[RealityName] [nvarchar](10) NULL,
[Integral] [int] NOT NULL,
[ParentId] [varchar](20) NULL,
[RoleId] [int] NOT NULL,
[PswHash] [varchar](50) NOT NULL,
[PswSalt] [varchar](50) NOT NULL,
[PswFormat] [int] NOT NULL,
[PswExpiredDate] [datetime] NULL,
[Province] [int] NOT NULL,
[City] [int] NOT NULL,
[Area] [int] NOT NULL,
[Address] [nvarchar](50) NULL,
[QQ] [varchar](10) NULL,
[EMail] [varchar](25) NOT NULL,
[TelPhone] [varchar](40) NULL,
[Mobile] [varchar](40) NULL,
[Birthday] [datetime] NULL,
[IDcards] [varchar](18) NOT NULL,
[HeadImg] [varchar](150) NULL,
[Sex] [int] NOT NULL,
[LoginIp] [varchar](15) NULL,
[LoginTime] [datetime] NOT NULL,
[LastLoginIp] [varchar](15) NULL,
[LastLoginTime] [datetime] NULL,
[LastLoginGame] [int] NULL,
[GameArea] [int] NULL,
[LastLoginGameTime] [datetime] NULL,
[AmountTotal] [money] NOT NULL,
[AmountInUse] [money] NOT NULL,
[Balance] [money] NOT NULL,
[Credit] [money] NOT NULL,
[UserStatus] [int] NOT NULL,
[UnFreezeTime] [datetime] NULL,
[StampUserId] [varchar](20) NULL,
[ExpiredDate] [datetime] NULL,
[CreateTime] [datetime] NOT NULL,
[LastUpdateTime] [datetime] NOT NULL,
[UpdateCount] [int] NOT NULL,
[ValidateStatus] [int] NOT NULL,
[ValidateCode] [char](6) NULL,
[tag] int not null,
SecuritySet nvarchar(200) null
)	
declare @rowCount int,@tag int,@tagnext int
set @tag=1
INSERT INTO @usertbl ([seqno],[UserId],[NickName],[RealityName],[Integral],[ParentId],[RoleId],[PswHash],[PswSalt],[PswFormat],[PswExpiredDate],[Province],[City],[Area],[Address],[QQ],[EMail],[TelPhone],[Mobile],[Birthday],[IDcards],[HeadImg],[Sex],[LoginIp],[LoginTime],[LastLoginIp],[LastLoginTime],[LastLoginGame],[GameArea],[LastLoginGameTime],[AmountTotal],[AmountInUse],[Balance],[Credit],[UserStatus],[UnFreezeTime],[StampUserId],[ExpiredDate],[CreateTime],[LastUpdateTime],[UpdateCount],[ValidateStatus],[ValidateCode],[tag],SecuritySet) 
SELECT [seqno],[UserId],[NickName],[RealityName],[Integral],[ParentId],[RoleId],[PswHash],[PswSalt],[PswFormat],[PswExpiredDate],[Province],[City],[Area],[Address],[QQ],[EMail],[TelPhone],[Mobile],[Birthday],[IDcards],[HeadImg],[Sex],[LoginIp],[LoginTime],[LastLoginIp],[LastLoginTime],[LastLoginGame],[GameArea],[LastLoginGameTime],[AmountTotal],[AmountInUse],[Balance],[Credit],[UserStatus],[UnFreezeTime],[StampUserId],[ExpiredDate],[CreateTime],[LastUpdateTime],[UpdateCount],[ValidateStatus],[ValidateCode],@tag,dbo.f_getUserSecuritySet(userid) FROM UserInfo where ParentId=@UserId
select @rowCount=COUNT(UserId) from @usertbl
while(@rowCount>0)
begin
set @tagnext=@tag
set @tag +=1
INSERT INTO @usertbl ([seqno],[UserId],[NickName],[RealityName],[Integral],[ParentId],[RoleId],[PswHash],[PswSalt],[PswFormat],[PswExpiredDate],[Province],[City],[Area],[Address],[QQ],[EMail],[TelPhone],[Mobile],[Birthday],[IDcards],[HeadImg],[Sex],[LoginIp],[LoginTime],[LastLoginIp],[LastLoginTime],[LastLoginGame],[GameArea],[LastLoginGameTime],[AmountTotal],[AmountInUse],[Balance],[Credit],[UserStatus],[UnFreezeTime],[StampUserId],[ExpiredDate],[CreateTime],[LastUpdateTime],[UpdateCount],[ValidateStatus],[ValidateCode],[tag],SecuritySet) 
SELECT [seqno],[UserId],[NickName],[RealityName],[Integral],[ParentId],[RoleId],[PswHash],[PswSalt],[PswFormat],[PswExpiredDate],[Province],[City],[Area],[Address],[QQ],[EMail],[TelPhone],[Mobile],[Birthday],[IDcards],[HeadImg],[Sex],[LoginIp],[LoginTime],[LastLoginIp],[LastLoginTime],[LastLoginGame],[GameArea],[LastLoginGameTime],[AmountTotal],[AmountInUse],[Balance],[Credit],[UserStatus],[UnFreezeTime],[StampUserId],[ExpiredDate],[CreateTime],[LastUpdateTime],[UpdateCount],[ValidateStatus],[ValidateCode],@tagnext,dbo.f_getUserSecuritySet(userid) FROM UserInfo where ParentId in (select UserId from @usertbl where tag=@tagnext)
select @rowCount=COUNT(UserId) from @usertbl where tag=@tag	 
end
select * from (select ROW_NUMBER() over(order by {3}) as RowId,* from @usertbl where RoleId in ({0})) as O where O.RowId between {1} and {2}
select count(UserId) from @usertbl where RoleId in ({0})"
                , sbRoleList.ToString().TrimEnd(','), pageStartIndex, pageEndIndex, orderby);
            DataSet ds = DbHelperSQL.Query(sb.ToString(), new SqlParameter("@UserId", parentId));
            if (ds.Tables.Count != 2)
            {
                return null;
            }
            rowCount = ConvertUtil.ToInt(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }
        /// <summary>
        /// 返回输入用户是否拥有子级用户
        /// </summary>
        /// <param name="userId">需要查询的用户名</param>
        /// <returns>是/否</returns>
        public bool HasChild(string userId)
        {
            return DbHelperSQL.Exists("select count(UserId) from UserInfo where ParentId=@ParentId", new SqlParameter("@ParentId", userId));
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetListPromoter(List<string> fields, List<object> values)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@" select * FROM (
                            select row_number() over(order by u.UserId) as Row, u.*,c.CafesId,c.CafesName,c.Province as CProvince,c.City as CCity
                            ,c.Area as CArea,c.Address as CafeAdress,c.Description,c.CreateTime as CafeCreateTime from UserInfo u
                            left join CafesInfos c on u.UserId=c.UserId
                            where u.RoleId in (2,3,4,5)
                            ) as t where 1=1 ");
            List<SqlParameter> parm = new List<SqlParameter>();
            //定时器
            List<string> list = new List<string>();
            int num = 0;
            if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    //判断第几次用此参数
                    string sign = fields[i].Substring(fields[i].IndexOf('_') + 1);
                    num = GetCount(list, sign);
                    if (fields[i].StartsWith("like_"))
                    {
                        strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                    }
                    else if (fields[i].StartsWith("not_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                        }
                        else
                        {
                            strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + num + "  ");
                        }
                    }
                    else if (fields[i].StartsWith("lt_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                        }
                        else
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + num + "  ");
                        }
                    }
                    else if (fields[i].StartsWith("gt_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                        }
                        else
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + num + "  ");
                        }
                    }
                    else if (fields[i].StartsWith("&_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append(" AND  " + fields[i].Substring(2) + "&@" + fields[i].Substring(2) + "=@" + fields[i].Substring(2) + "   ");
                        }
                        else
                        {
                            strSql.Append(" AND  " + fields[i].Substring(2) + "&@" + fields[i].Substring(2) + num + "=@" + fields[i].Substring(2) + num + "   ");
                        }
                    }
                    else if (fields[i].StartsWith("&not_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append(" AND  " + fields[i].Substring(5) + "&@" + fields[i].Substring(5) + "!=@" + fields[i].Substring(5) + "   ");
                        }
                        else
                        {
                            strSql.Append(" AND  " + fields[i].Substring(5) + "&@" + fields[i].Substring(5) + num + "!=@" + fields[i].Substring(5) + num + "   ");
                        }
                    }
                    else
                    {
                        strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    }
                    //判断第几次用此参数
                    if (num == 0)
                    {
                        parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                    }
                    else
                    {
                        parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1) + num, values[i]));
                    }
                    list.Add(fields[i].Substring(fields[i].IndexOf('_') + 1));
                }
            }
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }
        //定时器
        public int GetCount(List<string> list, string sign)
        {
            if (string.IsNullOrEmpty(sign) || list == null || list.Count <= 0)
            {
                return 0;
            }
            int num = 0;
            for (int i = 0; i < list.Count; i++)
            {
                if (list[i] == sign)
                {
                    num++;
                }
            }
            return num;
        }
        #region 用户列表
        /// <summary>
        /// 查询我的玩家
        /// </summary>
        /// <param name="asstUserId">推广员用户名</param>
        /// <param name="user">玩家</param>
        /// <param name="type">玩家的查询类型</param>
        /// <param name="consumeMoneyStart">汇总金额最小值，如果输入0，则不采用下限</param>
        /// <param name="consumeMoneyEnd">汇总金额最大值，如果输入-1，则不采用上限</param>
        /// <param name="consumeDateStart">查询的交易开始时间，如果输入的是DateTime.MinValue，则不采用下限</param>
        /// <param name="consumeDateEnd">查询的交易结束时间，如果输入的是DateTime.MinValue，则不采用上限</param>
        /// <param name="startIndex">开始行号</param>
        /// <param name="endIndex">结束行号</param>
        /// <returns></returns>
        public DataSet GetPlayerList(string asstUserId, string user, UserIdInputType type, decimal consumeMoneyStart, decimal consumeMoneyEnd, DateTime consumeDateStart, DateTime consumeDateEnd, int startIndex, int endIndex)
        {
            if (string.IsNullOrEmpty(asstUserId))
            {
                return null;
            }
            StringBuilder sbSql = new StringBuilder();
            List<SqlParameter> lsp = new List<SqlParameter>();
            sbSql.AppendFormat(@"
declare @tbl table
(
	RowId	int identity(1,1),
	UserId	varchar(20) ,
	NickName nvarchar(20),
	RealityName nvarchar(50),
	Mobile varchar(40),
	EMail varchar(25),
	QQ varchar(10),
	Address nvarchar(50),
	CreateTime datetime,
	LastLoginTime datetime,
	Consume money
)
insert into @tbl select a.UserId,a.NickName
,case when a.RealityName is null then a.UserId else a.RealityName end
,a.Mobile,a.EMail,a.QQ,a.Address,a.CreateTime,a.LastLoginTime,c.Consume
from UserInfo as a 
left join UserInfo as b on a.UserId=b.UserId
left join 
(
select SUM(Amount) as Consume,a.UserId from GameCharge as a
left join UserInfo as b on a.UserId=b.UserId
 where b.ParentId=@AsstId and a.Status=0
");
            lsp.Add(new SqlParameter("@AsstId", SqlDbType.VarChar, 20) { Value = asstUserId });
            bool isLimtByCreateTime = false;
            if (consumeDateStart != DateTime.MinValue && consumeDateEnd != DateTime.MinValue && consumeDateEnd > consumeDateStart)
            {
                sbSql.AppendFormat(" and a.CreateTime between @ConsumeDateStart and @ConsumeDateEnd");
                lsp.Add(new SqlParameter("@ConsumeDateStart", SqlDbType.Date) { Value = consumeDateStart });
                lsp.Add(new SqlParameter("@ConsumeDateEnd", SqlDbType.Date) { Value = consumeDateEnd });
                isLimtByCreateTime = true;
            }
            else if (consumeDateStart == DateTime.MinValue && consumeDateEnd != DateTime.MinValue)
            {
                sbSql.AppendFormat(" and a.CreateTime < @ConsumeDateEnd");
                lsp.Add(new SqlParameter("@ConsumeDateEnd", SqlDbType.Date) { Value = consumeDateEnd });
                isLimtByCreateTime = true;
            }
            else if (consumeDateStart != DateTime.MinValue && consumeDateEnd == DateTime.MinValue)
            {
                sbSql.AppendFormat(" and a.CreateTime > @ConsumeDateStart");
                lsp.Add(new SqlParameter("@ConsumeDateStart", SqlDbType.Date) { Value = consumeDateStart });
                isLimtByCreateTime = true;
            }
            sbSql.AppendFormat(@" group by a.UserId
) as c on a.UserId=c.UserId
where a.ParentId=@AsstId");
            if (!string.IsNullOrEmpty(user))
            {
                string userFiled = string.Empty;
                switch (type)
                {
                    case UserIdInputType.UserId:
                        userFiled = "a.UserId";
                        break;
                    case UserIdInputType.UserName:
                        userFiled = "a.UserId";
                        break;
                    case UserIdInputType.Name:
                        userFiled = "a.RealityName";
                        break;
                    case UserIdInputType.NickName:
                        userFiled = "a.NickName";
                        break;
                    default:
                        return null;
                }
                sbSql.AppendFormat(" and {0} like @inpuUser",userFiled);
                lsp.Add(new SqlParameter("@inpuUser", SqlDbType.VarChar, 20) { Value = "%" +  user  + "%"});
            }
            if (consumeMoneyStart != 0 && consumeMoneyEnd != -1 && consumeMoneyEnd > consumeMoneyStart)
            {
                sbSql.AppendFormat(" and c.Consume between @ConsumeMoneyStart and @ConsumeMoneyEnd");
                lsp.Add(new SqlParameter("@ConsumeMoneyStart", SqlDbType.Decimal) { Value = consumeMoneyStart });
                lsp.Add(new SqlParameter("@ConsumeMoneyEnd", SqlDbType.Decimal) { Value = consumeMoneyEnd });
            }
            else if (consumeMoneyStart == 0 && consumeMoneyEnd != -1)
            {
                sbSql.AppendFormat(" and c.Consume < @ConsumeDateEnd");
                lsp.Add(new SqlParameter("@ConsumeMoneyEnd", SqlDbType.Decimal) { Value = consumeMoneyEnd });
            }
            else if (consumeMoneyStart != 0 && consumeMoneyEnd == -1)
            {
                sbSql.AppendFormat(" and c.Consume > @ConsumeDateStart");
                lsp.Add(new SqlParameter("@ConsumeMoneyStart", SqlDbType.Decimal) { Value = consumeMoneyStart });
            }
            lsp.Add(new SqlParameter("@StartIndex", SqlDbType.Decimal) { Value = startIndex });
            lsp.Add(new SqlParameter("@EndIndex", SqlDbType.Decimal) { Value = endIndex });
            sbSql.AppendFormat(" select COUNT(1) from @tbl select * from @tbl where RowId between @StartIndex and @EndIndex");
            if (isLimtByCreateTime)
            {
                sbSql.AppendFormat(" and Consume is not null");
            }
            return DbHelperSQL.Query(sbSql.ToString(), lsp.ToArray());
        }
        /// <summary>
        /// 获取连锁公司下的网吧列表
        /// </summary>
        /// <param name="parentId">连锁公司用户名</param>
        /// <param name="cafesName">网吧名称，采用模糊搜索</param>
        /// <param name="roleIdList">网吧角色编号列表</param>
        /// <param name="indexStart">获取的开始行号</param>
        /// <param name="indexEnd">获取的结束行号</param>
        /// <returns></returns>
        public DataSet GetCafesList(string parentId, string cafesName, List<int> roleIdList, int indexStart, int indexEnd)
        {
            if (roleIdList == null || roleIdList.Count == 0)
                return null;
            StringBuilder sbSql = new StringBuilder();
            sbSql.AppendFormat(@"declare @tbl table
(
	RowId	int identity(1,1),
	UserId	varchar(20) ,
	RealityName nvarchar(50),
	RoleId int,
	UserStatus int,	
	LastLoginTime datetime
)
insert into @tbl
select a.UserId,a.RealityName,a.RoleId,a.UserStatus,a.LastLoginTime from UserInfo as a
where a.ParentId=@parentId and a.UserStatus & 64!=64 and a.RoleId in (");
            List<SqlParameter> lsp = new List<SqlParameter>();
            for (int i = 0; i < roleIdList.Count; i++)
            {
                string spName = "@RoleId" + i;
                sbSql.AppendFormat("{0},", spName);
                lsp.Add(new SqlParameter(spName, SqlDbType.Int) { Value = roleIdList[i] });
            }
            sbSql.Remove(sbSql.Length - 1, 1);
            sbSql.Append(")");
            if (!string.IsNullOrEmpty(cafesName))
            {
                sbSql.Append(" and a.RealityName like @cafes_name ");
                lsp.Add(new SqlParameter("@cafes_name", SqlDbType.NVarChar, 30) { Value = "%{0}%".FormatWith(cafesName) });
            }
            sbSql.Append(@"
select COUNT(1) from @tbl
select t.*,ug.Status from @tbl t 
left join User_Grant ug on t.UserId=ug.UserId
where RowId between @indexStart and @indexEnd");
            lsp.Add(new SqlParameter("@parentId", SqlDbType.VarChar, 20) { Value = parentId });
            lsp.Add(new SqlParameter("@indexStart", SqlDbType.Int) { Value = indexStart });
            lsp.Add(new SqlParameter("@indexEnd", SqlDbType.Int) { Value = indexEnd });
            return DbHelperSQL.Query(sbSql.ToString(), lsp.ToArray());
        }
        /// <summary>
        /// 获取代理下的成员列表
        /// </summary>
        /// <param name="parentId">代理用户名</param>
        /// <param name="userName">用户名称，采用模糊搜索</param>
        /// <param name="roleIdList">角色编号列表</param>
        /// <param name="indexStart">获取的开始行号</param>
        /// <param name="indexEnd">获取的结束行号</param>
        /// <returns></returns>
        public DataSet GetMemberList(string parentId, string userName, List<int> roleIdList, int indexStart, int indexEnd)
        {
            if (roleIdList == null || roleIdList.Count == 0)
                return null;
            StringBuilder sbSql = new StringBuilder();
            sbSql.AppendFormat(@"declare @tbl table
(
	RowId	int identity(1,1),
	UserId	varchar(20) ,
	RealityName nvarchar(50),
	RoleId int,
	UserStatus int,	
	LastLoginTime datetime
)
insert into @tbl
select a.UserId,a.RealityName,a.RoleId,a.UserStatus,a.LastLoginTime from UserInfo as a
where a.ParentId=@parentId and a.UserStatus & 64!=64 and a.RoleId in (");
            List<SqlParameter> lsp = new List<SqlParameter>();
            for (int i = 0; i < roleIdList.Count; i++)
            {
                string spName = "@RoleId" + i;
                sbSql.AppendFormat("{0},", spName);
                lsp.Add(new SqlParameter(spName, SqlDbType.Int) { Value = roleIdList[i] });
            }
            sbSql.Remove(sbSql.Length - 1, 1);
            sbSql.Append(")");
            if (!string.IsNullOrEmpty(userName))
            {
                sbSql.Append(" and a.RealityName like @user_name ");
                lsp.Add(new SqlParameter("@user_name", SqlDbType.NVarChar, 30) { Value = "%{0}%".FormatWith(userName) });
            }
            sbSql.Append(@"
select COUNT(1) from @tbl
select * from @tbl where RowId between @indexStart and @indexEnd");
            lsp.Add(new SqlParameter("@parentId", SqlDbType.VarChar, 20) { Value = parentId });
            lsp.Add(new SqlParameter("@indexStart", SqlDbType.Int) { Value = indexStart });
            lsp.Add(new SqlParameter("@indexEnd", SqlDbType.Int) { Value = indexEnd });
            return DbHelperSQL.Query(sbSql.ToString(), lsp.ToArray());
        }
        /// <summary>
        /// 获取网吧下的推广员列表
        /// </summary>
        /// <param name="parentId">代理用户名</param>
        /// <param name="userName">用户名称，采用模糊搜索</param>
        /// <param name="indexStart">获取的开始行号</param>
        /// <param name="indexEnd">获取的结束行号</param>
        /// <returns></returns>
        public DataSet GetAsstList(string parentId, string userName, int indexStart, int indexEnd)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.AppendFormat(@"
declare @tbl table
(
	RowId	int identity(1,1),
	UserId	varchar(20) ,
	RealityName nvarchar(50),
	Mobile varchar(40),
	EMail varchar(25),
	QQ varchar(10),
	CreateTime datetime
)
insert into @tbl
select b.UserId,b.RealityName,b.Mobile,b.Email,b.QQ,b.CreateTime from UserRelation as a
left join UserInfo as b on a.UserId=b.UserId
where a.ParentId=@parentId and b.UserStatus & 2048!=2048 and a.Type=1");
            List<SqlParameter> lsp = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(userName))
            {
                sbSql.Append(" and b.RealityName like @user_name ");
                lsp.Add(new SqlParameter("@user_name", SqlDbType.NVarChar, 30) { Value = "%{0}%".FormatWith(userName) });
            }
            sbSql.Append(@"
select COUNT(1) from @tbl
select * from @tbl where RowId between @indexStart and @indexEnd");
            lsp.Add(new SqlParameter("@parentId", SqlDbType.VarChar, 20) { Value = parentId });
            lsp.Add(new SqlParameter("@indexStart", SqlDbType.Int) { Value = indexStart });
            lsp.Add(new SqlParameter("@indexEnd", SqlDbType.Int) { Value = indexEnd });
            return DbHelperSQL.Query(sbSql.ToString(), lsp.ToArray());
        }
        #endregion
        #region 审核
        /// <summary>
        /// 获取审核列表
        /// </summary>
        /// <param name="parentId">代理的用户名</param>
        /// <param name="lsRoleId">查询的待审核的角色编号列表</param>
        /// <param name="lsRoleId">查询的待审核的用户状态列表</param>
        /// <param name="indexStart">开始行号</param>
        /// <param name="indexEnd">结束行号</param>
        /// <returns></returns>
        public DataSet GetExamineList(string parentId, List<int> lsRoleId, List<int> lsUserStatus, int indexStart, int indexEnd)
        {
            if (lsRoleId == null || lsRoleId.Count == 0 || lsUserStatus == null || lsUserStatus.Count == 0)
                return null;
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(@"
declare @tbl table
(
	RowId	int identity(1,1),
	UserId	varchar(20) ,
	RealityName nvarchar(50),
	RoleName nvarchar(50),	
    RoleId int,
	SUserStatus nvarchar(50),
	UserStatus nvarchar(50),	
	CreateTime datetime,
	LastUpdateTime datetime,
	Examiner nvarchar(50),
    ExaminerId varchar(20),
	ActivateExamineTime datetime,
	timespan int	
)
insert into @tbl select 
a.UserId
,case when a.RealityName is null then a.UserId else a.RealityName end
,b.RoleName,a.RoleId,dbo.f_getUserUserStatusAudit(a.UserStatus) as AuditStatus
,a.UserStatus
,a.CreateTime,a.LastUpdateTime
,case when c.RealityName is null then d.Examiner else c.RealityName end
,d.Examiner
,d.ActivateExamineTime,DATEDIFF(hour,d.ActivateExamineTime, GETDATE()) as timespan
 from UserInfo as a
left join RoleList as b on a.RoleId=b.RoleID   
left join UserExp as d on a.UserId=d.UserId   
left join UserInfo as c on d.Examiner=c.UserId
where a.ParentId=@parentId and a.RoleId in(");
            List<SqlParameter> lsp = new List<SqlParameter>();
            for (int i = 0; i < lsRoleId.Count; i++)
            {
                string spName = "@RoleId" + i;
                sbSql.AppendFormat("{0},", spName);
                lsp.Add(new SqlParameter(spName, SqlDbType.Int) { Value = lsRoleId[i] });
            }
            sbSql.Remove(sbSql.Length - 1, 1);
            sbSql.Append(") and (");
            for (int i = 0; i < lsUserStatus.Count; i++)
            {
                string spName = "@status" + i;
                sbSql.AppendFormat("a.UserStatus &{0}={0} or", spName);
                lsp.Add(new SqlParameter(spName, SqlDbType.Int) { Value = lsUserStatus[i] });
            }
            sbSql.Remove(sbSql.Length - 3, 3);
            sbSql.Append(@")
select COUNT(1) from @tbl
select * from @tbl where RowId between @indexStart and @indexEnd");
            lsp.AddRange(new SqlParameter[]
            {
                 new SqlParameter("@parentId", SqlDbType.VarChar, 20) { Value = parentId }
                , new SqlParameter("@indexStart", SqlDbType.Int) { Value = indexStart }
                , new SqlParameter("@indexEnd", SqlDbType.Int) { Value = indexEnd }
            });
            return DbHelperSQL.Query(sbSql.ToString(), lsp.ToArray());
        }
        /// <summary>
        /// 获取平台管理员的审核列表
        /// </summary>
        /// <param name="indexStart">开始行号</param>
        /// <param name="indexEnd">结束行号</param>
        /// <returns></returns>
        public DataSet GetExamineList_Root(int indexStart, int indexEnd)
        {
            return DbHelperSQL.Query(@"
declare @tbl table
(
	RowId	int identity(1,1),
	UserId	varchar(20) ,
	RealityName nvarchar(50),
	RoleName nvarchar(50),	
    RoleId int,
	SUserStatus nvarchar(50),
	UserStatus nvarchar(50),	
	CreateTime datetime,
	LastUpdateTime datetime,
	Examiner nvarchar(50),
    ExaminerId varchar(20),
	ActivateExamineTime datetime,
	timespan int	
)
insert into @tbl select 
a.UserId
,case when a.RealityName is null then a.UserId else a.RealityName end
,b.RoleName,a.RoleId,dbo.f_getUserUserStatusAudit(a.UserStatus) as AuditStatus
,a.UserStatus
,a.CreateTime,a.LastUpdateTime
,case when c.RealityName is null then d.Examiner else c.RealityName end
,d.Examiner
,d.ActivateExamineTime,DATEDIFF(hour,d.ActivateExamineTime, GETDATE()) as timespan
 from UserInfo as a
left join RoleList as b on a.RoleId=b.RoleID   
left join UserExp as d on a.UserId=d.UserId   
left join UserInfo as c on d.Examiner=c.UserId
where a.UserStatus &64=64 and ((a.ParentId='root' and a.RoleId in (1,3)) or a.roleid in (2,4))
select COUNT(1) from @tbl
select * from @tbl where RowId between @indexStart and @indexEnd"
                , new SqlParameter("@indexStart", SqlDbType.Int) { Value = indexStart }
                , new SqlParameter("@indexEnd", SqlDbType.Int) { Value = indexEnd }
                );
        }
        /// <summary>
        /// 获取用户审核历史列表
        /// </summary>
        /// <param name="target">被审核的用户标识输入</param>
        /// <param name="type">被审核的用户标识输入类型</param>
        /// <param name="examineTimeStart">查询的审核时间起始</param>
        /// <param name="examineTimeEnd">查询的审核时间的结束</param>        
        /// <param name="agentId">代理的用户名</param>
        /// <param name="indexStart">开始行号</param>
        /// <param name="indexEnd">结束行号</param>
        public DataSet GetExamineHistoryList(string target, UserIdInputType type, DateTime examineTimeStart, DateTime examineTimeEnd, string agentId, int indexStart, int indexEnd)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(@"
declare @tbl table
(
	RowId	int identity(1,1),
	ExType int,
	targetId	varchar(20) ,
	targetName nvarchar(50),
	ExamineUserId varchar(20),	
	examineUserName nvarchar(50),
	Status int,
	ExContent nvarchar(50),
	CreateTime datetime
)
insert into @tbl
select a.ExType,a.UserId as targetId
,case when b.RealityName is null then a.UserId else b.RealityName end as targetName
,a.ExamineUserId
,case when c.RealityName is null then a.ExamineUserId else c.RealityName end as examineUserName
,a.Status,a.ExContent,a.CreateTime  from ExamineInfo as a
left join UserInfo as b on a.UserId=b.UserId
left join UserInfo as c on a.ExamineUserId=c.UserId
where (a.ExamineUserId=@parentId or (c.ParentId=@parentId and c.RoleId=7))");
            List<SqlParameter> lsp = new List<SqlParameter>();
            if (examineTimeStart != DateTime.MinValue && examineTimeEnd != DateTime.MinValue && examineTimeEnd > examineTimeStart)
            {
                sbSql.Append(" and (a.CreateTime > @examineTimeStart and a.CreateTime < @examineTimeEnd)");
                lsp.Add(new SqlParameter("@examineTimeStart", SqlDbType.Date) { Value = examineTimeStart });
                lsp.Add(new SqlParameter("@examineTimeEnd", SqlDbType.Date) { Value = examineTimeEnd });
            }
            else if (examineTimeStart == DateTime.MinValue && examineTimeEnd != DateTime.MinValue)
            {
                sbSql.Append(" and a.CreateTime < @examineTimeEnd");
                lsp.Add(new SqlParameter("@examineTimeEnd", SqlDbType.Date) { Value = examineTimeEnd });
            }
            else if (examineTimeStart != DateTime.MinValue && examineTimeEnd == DateTime.MinValue)
            {
                sbSql.Append(" and a.CreateTime > @examineTimeStart");
                lsp.Add(new SqlParameter("@examineTimeStart", SqlDbType.Date) { Value = examineTimeStart });
            }
            if (!string.IsNullOrEmpty(target))
            {
                string userFiled = string.Empty;
                switch (type)
                {
                    case UserIdInputType.UserId:
                        userFiled = "a.UserId";
                        break;
                    case UserIdInputType.UserName:
                        userFiled = "a.UserId";
                        break;
                    case UserIdInputType.Name:
                        userFiled = "b.RealityName";
                        break;
                    case UserIdInputType.NickName:
                        userFiled = "b.NickName";
                        break;
                    default:
                        return null;
                }
                sbSql.AppendFormat(" and {0} like @inpuUser", userFiled);
                lsp.Add(new SqlParameter("@inpuUser", SqlDbType.VarChar, 20) { Value = "%" + target + "%" });
            }
            sbSql.Append(@" order by a.CreateTime
select COUNT(1) from @tbl
select * from @tbl where RowId between @indexStart and @indexEnd");
            lsp.AddRange(new SqlParameter[]
            {
                 new SqlParameter("@parentId", SqlDbType.VarChar, 20) { Value = agentId }
                , new SqlParameter("@indexStart", SqlDbType.Int) { Value = indexStart }
                , new SqlParameter("@indexEnd", SqlDbType.Int) { Value = indexEnd }}
            );
            return DbHelperSQL.Query(sbSql.ToString(), lsp.ToArray());
        }
        /// <summary>
        /// 获取审核列表
        /// </summary>
        /// <param name="parentId">网吧用户名</param>
        /// <param name="indexStart">开始行号</param>
        /// <param name="indexEnd">结束行号</param>
        /// <returns></returns>
        public DataSet GetExamineList_Asst(string parentId, int indexStart, int indexEnd)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(@"
declare @tbl table
(
	RowId	int identity(1,1),
	UserId	varchar(20) ,
	RealityName nvarchar(50),
	RoleName nvarchar(50),	
    RoleId int,
	SUserStatus nvarchar(50),
	UserStatus nvarchar(50),	
	CreateTime datetime,
	LastUpdateTime datetime,
	Examiner nvarchar(50),
    ExaminerId varchar(20),
	ActivateExamineTime datetime,
	timespan int	
)
insert into @tbl select 
a.UserId
,case when a.RealityName is null then a.UserId else a.RealityName end
,b.RoleName,a.RoleId,dbo.f_getUserUserStatusAudit(a.UserStatus) as AuditStatus
,a.UserStatus
,a.CreateTime,a.LastUpdateTime
,case when c.RealityName is null then d.Examiner else c.RealityName end
,d.Examiner
,d.ActivateExamineTime,DATEDIFF(hour,d.ActivateExamineTime, GETDATE()) as timespan
 from UserRelation as a1
left join UserInfo as a on a1.UserId=a.UserId
left join RoleList as b on a.RoleId=b.RoleID   
left join UserExp as d on a.UserId=d.UserId   
left join UserInfo as c on d.Examiner=c.UserId
where a1.ParentId=@parentId and a1.Type=1 and a.UserStatus &2048=2048
select COUNT(1) from @tbl
select * from @tbl where RowId between @indexStart and @indexEnd");
            List<SqlParameter> lsp = new List<SqlParameter>();
            lsp.AddRange(new SqlParameter[]
            {
                 new SqlParameter("@parentId", SqlDbType.VarChar, 20) { Value = parentId }
                , new SqlParameter("@indexStart", SqlDbType.Int) { Value = indexStart }
                , new SqlParameter("@indexEnd", SqlDbType.Int) { Value = indexEnd }
            });
            return DbHelperSQL.Query(sbSql.ToString(), lsp.ToArray());
        }
        #endregion
        /// <summary>
        /// 获取记录总数(base CreditAdJustHistory)
        /// </summary>
        public int GetRecordCountUnionCredit(List<string> fields, List<object> values)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select count(1) FROM (
                            select * from (
                            select u.*,c.CafesId,c.CafesName,c.Address as CAddress,c.Description,c.CreateTime as CCreateTime
                            ,pu.NickName as PNickName,p.CityName as PProvince,pc.CityName as PCity,pa.CityName as PArea
                            ,c.Province as CProvince,c.City as CCity,c.Area as CArea
							,cp.CityName as CProvinceName,cc.CityName as CCityName,ca.CityName as CAreaName
                            from UserInfo u
                            left join CafesInfos c on u.UserId=c.UserId
                            left join UserInfo pu on u.ParentId=pu.UserId
                            left join Province p on p.ID=pu.Province
                            left join Province pc on pc.ID=pu.City
                            left join Province pa on pa.ID=pu.Area
							left join Province cp on cp.ID=c.Province
							left join Province cc on cc.ID=c.City
							left join Province ca on ca.ID=c.Area 
                            ) T where UserId in 
                            (
                            select distinct UserId from CreditAdjustHistory
                            )
                            ) as TT where 1=1  ");
            List<SqlParameter> parm = new List<SqlParameter>(); if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    if (fields[i].StartsWith("like_"))
                    {
                        strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                    }
                    else if (fields[i].StartsWith("not_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                    }
                    else if (fields[i].StartsWith("lt_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                    }
                    else if (fields[i].StartsWith("gt_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                    }
                    else
                    {
                        strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    }
                    parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                }
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parm.ToArray());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表(base CreditAdJustHistory)
        /// </summary>
        public DataSet GetListByPageUnionCredit(List<string> fields, List<object> values, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT TT.* FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.UserId desc");
            }
            strSql.Append(@")AS Row, T.*  from (
                            select u.*,c.CafesId,c.CafesName,c.Address as CAddress,c.Description,c.CreateTime as CCreateTime
                            ,pu.NickName as PNickName,p.CityName as PProvince,pc.CityName as PCity,pa.CityName as PArea
                            ,c.Province as CProvince,c.City as CCity,c.Area as CArea
							,cp.CityName as CProvinceName,cc.CityName as CCityName,ca.CityName as CAreaName
                            from UserInfo u
                            left join CafesInfos c on u.UserId=c.UserId
                            left join UserInfo pu on u.ParentId=pu.UserId
                            left join Province p on p.ID=pu.Province
                            left join Province pc on pc.ID=pu.City
                            left join Province pa on pa.ID=pu.Area 
							left join Province cp on cp.ID=c.Province
							left join Province cc on cc.ID=c.City
							left join Province ca on ca.ID=c.Area
                            ) T where UserId in 
                            (
                            select distinct UserId from CreditAdjustHistory
                            )  ");
            List<SqlParameter> parm = new List<SqlParameter>();
            if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    if (fields[i].StartsWith("like_"))
                    {
                        strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                    }
                    else if (fields[i].StartsWith("not_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                    }
                    else if (fields[i].StartsWith("lt_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                    }
                    else if (fields[i].StartsWith("gt_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                    }
                    else
                    {
                        strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    }
                    parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                }
            }
            strSql.Append(" ) as TT ");
            //strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }
        /// <summary>
        /// 返回父级账户下面的子账户角色
        /// </summary>
        /// <param name="parentid">父ID</param>
        /// <param name="roleid">角色ID，为NULL所用</param>
        /// <returns></returns>
        public DataTable GetUserFromParent(string parentid, string roleid)
        {
            SqlParameter[] parameters = new SqlParameter[]{
                new SqlParameter("@ParentId",SqlDbType.VarChar,20),
                new  SqlParameter("@roleId",SqlDbType.VarChar,50)
            };
            parameters[0].Value = parentid;
            parameters[1].Value = roleid;
            return DbHelperSQL.ProcedureQury("QuerySonUserIdFormParent", parameters);
        }
        //user表的模糊查询
        public List<UserInfo> GetCheckName(List<string> fields, List<object> values)
        {
            List<UserInfo> list = new List<UserInfo>();
            UserInfo temp = new UserInfo();
            if (fields.Count <= 0 || values.Count <= 0)
            {
                return list;
            }
            StringBuilder strSql = new StringBuilder();
            StringBuilder unionSql = new StringBuilder();
            List<SqlParameter> parm = new List<SqlParameter>();
            strSql.Append("select * from UserInfo where 1=1 ");
            int getUnion = 0;
            //定时器
            List<string> listclock = new List<string>();
            int num = 0;
            //处理结束
            if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    //判断第几次用此参数
                    string sign = fields[i].Substring(fields[i].IndexOf('_') + 1);
                    num = GetCount(listclock, sign);
                    if (fields[i].StartsWith("like_"))
                    {
                        strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                    }
                    else if (fields[i].StartsWith("halflike_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(9) + " like @" + fields[i].Substring(9));
                    }
                    else if (fields[i].StartsWith("not_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                        }
                        else
                        {
                            strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + num + "  ");
                        }
                    }
                    else if (fields[i].StartsWith("all_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append("  AND " + fields[i].Substring(4) + "=@" + fields[i].Substring(4) + "  ");
                        }
                        else
                        {
                            strSql.Append("  AND " + fields[i].Substring(4) + "=@" + fields[i].Substring(4) + num + "  ");
                        }
                        getUnion++;
                    }
                    else if (fields[i].StartsWith("lt_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                        }
                        else
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + num + "  ");
                        }
                    }
                    else if (fields[i].StartsWith("gt_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                        }
                        else
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + num + "  ");
                        }
                    }
                    else if (fields[i].StartsWith("&_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append(" AND  " + fields[i].Substring(2) + "&@" + fields[i].Substring(2) + "=@" + fields[i].Substring(2) + "   ");
                        }
                        else
                        {
                            strSql.Append(" AND  " + fields[i].Substring(2) + "&@" + fields[i].Substring(2) + num + "=@" + fields[i].Substring(2) + num + "   ");
                        }
                    }
                    else if (fields[i].StartsWith("&not_"))
                    {
                        if (num == 0)
                        {
                            strSql.Append(" AND  " + fields[i].Substring(5) + "&@" + fields[i].Substring(5) + "!=@" + fields[i].Substring(5) + "   ");
                        }
                        else
                        {
                            strSql.Append(" AND  " + fields[i].Substring(5) + "&@" + fields[i].Substring(5) + num + "!=@" + fields[i].Substring(5) + num + "   ");
                        }
                    }
                    else
                    {
                        if (num == 0)
                        {
                            strSql.Append(" AND (" + fields[i] + "=@" + fields[i] + " ");
                            int num1 = 0;
                            for (int j = i + 1; j < fields.Count; j++)
                            {
                                if (fields[j] == fields[i])
                                {
                                    num1++;
                                }
                            }
                            if (num1 == 0)
                            {
                                strSql.Append(" ) ");
                            }
                        }
                        else
                        {
                            strSql.Append(" OR " + fields[i] + "=@" + fields[i] + num + " ");
                            int num1 = 0;
                            for (int j = i + 1; j < fields.Count; j++)
                            {
                                if (fields[j] == fields[i])
                                {
                                    num1++;
                                }
                            }
                            if (num1 == 0)
                            {
                                strSql.Append(" ) ");
                            }
                        }
                    }
                    //判断第几次用此参数
                    if (num == 0)
                    {
                        if (fields[i].StartsWith("halflike_"))
                        {
                            parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i] + "%"));
                        }
                        else
                        {
                            parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                        }
                    }
                    else
                    {
                        parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1) + num, values[i]));
                    }
                    listclock.Add(fields[i].Substring(fields[i].IndexOf('_') + 1));
                }
            }
            if (getUnion > 0)
            {
                string union = strSql.ToString();
                union = union.Replace("*", "UserId");
                unionSql.Append(@" union 
                                            (
                                            select * from UserInfo
                                            where ParentId in (");
                unionSql.Append(union);
                unionSql.Append(@" )
                                   ) ");
            }
            strSql.Append(unionSql);
            //for (int i = 0; i < fields.Count; i++)
            //{
            //    if (fields[i].StartsWith("like_"))
            //    {
            //        strSql.Append(" and UserId like @" + fields[i].Substring());
            //    }
            //    parm.Add(new SqlParameter("@" + fields[i], values[i] + "%"));
            //}
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
            if (ds == null || ds.Tables[0].Rows.Count <= 0)
            {
                return list;
            }
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                temp = new UserInfo();
                if (ds.Tables[0].Rows[i]["UserId"] != null || ds.Tables[0].Rows[i]["UserId"].ToString() != "")
                {
                    temp.UserId = ds.Tables[0].Rows[i]["UserId"].ToString();
                }
                if (ds.Tables[0].Rows[i]["ParentId"] != null || ds.Tables[0].Rows[i]["ParentId"].ToString() != "")
                {
                    temp.ParentId = ds.Tables[0].Rows[i]["ParentId"].ToString();
                }
                list.Add(temp);
            }
            return list;
        }
        ////定时器
        //public int GetCount(List<string> list, string sign)
        //{
        //    if (string.IsNullOrEmpty(sign) || list == null || list.Count <= 0)
        //    {
        //        return 0;
        //    }
        //    int num = 0;
        //    for (int i = 0; i < list.Count; i++)
        //    {
        //        if (list[i] == sign)
        //        {
        //            num++;
        //        }
        //    }
        //    return num;
        //}
        /// <summary>
        /// 获取子账号列表
        /// </summary>
        /// <param name="fields">请在定义中声明PRoleId(需要查询子账号的主账号ID)</param>
        /// <param name="values"></param>
        /// <returns></returns>
        public DataSet GetAccountList(List<string> fields, List<object> values)
        {
            if (fields.Count <= 0 || values.Count <= 0)
            {
                return null;
            }
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            List<SqlParameter> parm = new List<SqlParameter>();
            strSql.Append(@" select ui.UserId,usi.ConfigKey,usi.BoundValue,usi.Status,ui.RoleId,ui.EMail,ui.Mobile from UserInfo ui 
                            left join UserSafeInfos usi on usi.UserId = ui.UserId
                            where ui.UserId in (
                            select UserId from UserInfo where RoleId in 
                            (
                            select cil.ItemValue from ItemList il 
                            left join ItemDic id on il.ItemID=id.ItemParentId
                            left join ItemList cil on id.ItemId = cil.ItemID
                            where il.ItemText like 'RoleInfo%'   ");
            strSql1.Append(" ))  ");
            for (int i = 0; i < fields.Count; i++)
            {
                if (fields[i].StartsWith("like_"))
                {
                    strSql1.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                }
                else if (fields[i].StartsWith("PRoleId"))
                {
                    strSql.Append("  AND il.ItemValue=@" + fields[i] + "  ");
                }
                else if (fields[i].StartsWith("not_"))
                {
                    strSql1.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                }
                else if (fields[i].StartsWith("lt_"))
                {
                    strSql1.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                }
                else if (fields[i].StartsWith("gt_"))
                {
                    strSql1.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                }
                else
                {
                    strSql1.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                }
                parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
            }
            //strSql1.Append(" )  and ConfigKey like 'Receive_%' ");
            strSql.Append(strSql1);
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }
        /// <summary>
        /// 获得数据列表（获取区域代理用户信息）
        /// </summary>
        public DataSet GetList8(List<string> fields, List<object> values)
        {
            StringBuilder strSql = new StringBuilder();
            //strSql.Append("select * ");
            //strSql.Append(" FROM UserInfo where 1=1 ");
            strSql.Append(@"    select * from (
                                select a.UserId,a.Area,a.DomainArea,u.RealityName,u.RoleId,u.ParentId,u.UserStatus from AgentArea a
                                left join UserInfo u on a.UserId=u.UserId
                                ) t where 1=1   ");
            List<SqlParameter> parm = new List<SqlParameter>(); if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    if (fields[i].ToLower().StartsWith("like_")) { strSql.Append(" AND CHARINDEX(@" + fields[i].Split('_')[1] + "," + fields[i].Split('_')[1] + ")>0 "); }
                    else if (fields[i].ToLower().StartsWith("start_")) { strSql.Append(" AND DATEDIFF(day,@" + fields[i].Split('_')[1] + "," + fields[i].Split('_')[1] + ")>=0"); }
                    else if (fields[i].ToLower().StartsWith("end_")) { strSql.Append(" AND DATEDIFF(day,@" + fields[i].Split('_')[1] + "," + fields[i].Split('_')[1] + ")<=0"); }
                    else if (fields[i].ToLower().StartsWith("and_"))
                    {
                        strSql.Append(" AND (" + fields[i].Split('_')[1] + "& @" + fields[i].Split('_')[1] + ")=@" + fields[i].Split('_')[1] + " ");
                    }
                    else if (fields[i].ToLower().StartsWith("!and_"))
                    {
                        strSql.Append(" AND (" + fields[i].Split('_')[1] + "& @" + fields[i].Split('_')[1] + ")!=@" + fields[i].Split('_')[1] + " ");
                    }
                    else { strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " "); }
                    parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                }
            }
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }
        /// <summary>
        /// 获得数据列表（获取连锁公司用户信息）
        /// </summary>
        public DataSet GetList9(List<string> fields, List<object> values)
        {
            StringBuilder strSql = new StringBuilder();
            //strSql.Append("select * ");
            //strSql.Append(" FROM UserInfo where 1=1 ");
            strSql.Append(@"    select * from UserInfo where 1=1   ");
            List<SqlParameter> parm = new List<SqlParameter>();
            if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    if (fields[i].ToLower().StartsWith("like_")) { strSql.Append(" AND CHARINDEX(@" + fields[i].Split('_')[1] + "," + fields[i].Split('_')[1] + ")>0 "); }
                    else if (fields[i].ToLower().StartsWith("start_")) { strSql.Append(" AND DATEDIFF(day,@" + fields[i].Split('_')[1] + "," + fields[i].Split('_')[1] + ")>=0"); }
                    else if (fields[i].ToLower().StartsWith("end_")) { strSql.Append(" AND DATEDIFF(day,@" + fields[i].Split('_')[1] + "," + fields[i].Split('_')[1] + ")<=0"); }
                    else if (fields[i].ToLower().StartsWith("and_"))
                    {
                        strSql.Append(" AND (" + fields[i].Split('_')[1] + "& @" + fields[i].Split('_')[1] + ")=@" + fields[i].Split('_')[1] + " ");
                    }
                    else if (fields[i].ToLower().StartsWith("!and_"))
                    {
                        strSql.Append(" AND (" + fields[i].Split('_')[1] + "& @" + fields[i].Split('_')[1] + ")!=@" + fields[i].Split('_')[1] + " ");
                    }
                    else { strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " "); }
                    parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                }
            }
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }
    }
}
